transfer field between forms 1

[antonyx]

ok, slight problem.

the job table and the job pickup table are linked using the job id.

i have just discovered the button feature of access. i have used a button to open a form, and if the job is present in both tables then the subsequent record is found and displayed in the relevant form.

eg. if job id 1 has pickup details, then they will be displayed in the job pickup form when the button is pressed.

if its a new job, job id 5 for example (which doesnt have any pickup details registered), then it simply opens the job pickup form blank.. i would want the job id from the first form to be transfered into the job id field of the second form.

here is the code on my job form.

Option Compare Database

Private Sub Command6_Click()
On Error GoTo Err_Command6_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "job pickup"
    
    stLinkCriteria = "[job id]=" & Me![job id]
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me.[job id]

Exit_Command6_Click:
    Exit Sub

Err_Command6_Click:
    MsgBox Err.Description
    Resume Exit_Command6_Click
    
End Sub

and here is the code for my job pickup form

Option Compare Database

Private Sub Form_BeforeInsert(Cancel As Integer)
Me.[job id] = Me.OpenArgs
End Sub

help please!

 

[stix4t2]

Replace this code with the new stuff. You don't need to use bookmarks cause you are filtering to only the detail records for the job. If you want to automatically jump to the new record, then you may want to keep that code. I guess you would call this the development process, throwing away one way to replace with a better way. But the other lesson here is to discuss all requirements up front and then develop to those requirements. Its a journey!!!

 

[antonyx]

this is causing errors.

with this

Option Compare Database
option explicit

Private Sub Form_Load()
me.filter = "[job id] = " & Forms!job![job id]
me.filteron = true
me.[job id].defaultvalue = Forms!job![job id]
End Sub


in my job pickup form... when i press the button on my job form to open it i get this error 438, object doesnt support this property or method..

and in the job pickup form, when i tab to the next record.. the job id field is still blank when it should be the same job id as the previous record.

 

[antonyx]

the error is on this line apparently

Me.[job id].DefaultValue = Forms!job![job id]

 

[antonyx]

ok i just renamed the job id control and it works

just lastly, do you think this system is efficient and will it ensure that there are no dirty records.. is it a good way of doing things in the long term

 

[stix4t2]

For the situation of building associated records to the main form it is a good way to do it. It's simple and direct and fulfills your requirements.

However, you may have used a subform to accomplish the same thing. With the subform, you set the parent/child fields that tie the two datasets together. So no code is necessary in either form. And if you say there is not enough real estate to show the job entry and job pickup, you may consider using a tab control. The user wouldn't have to click to show detail form, or close detail form when complete. You could also make the sub detail form continuous to show the multiple details and allow review and edits to all details in one view. One tab for job entry and one tab for details. It really has to do with the how important the details are to the user.

You may also consider adding near the detail button a field with DCount("*", "Job", "JobID = " & me.[job id]). This will give the user a count of details for that job. You could instead of putting a label, use the control source for the whole string like:
= "Details: " & DCount("*", "Job", "JobID = " & me.[job id])
which would look like this if there were 2 detail records
Details: 2
Usually these fields are locked, on the data tab, and use the background colors instead of making it look like an entry field.

And lastly if you want to wow your friends and go works, if you use the tab control, put code in the form current to use the same detail formula for the caption on the details tab.

Think if you were the user how would you want to use it. What would you want to know. What would reduce errors or misunderstandings.

Good Luck

 

[ProCatHerder]

stix4t2

I am using the tab/form/subform design you mentioned as an altenate method to handle passing data between forms. I have a main form (SWMP) that allows the user to select a value from a combo control. I then have several tabs each containing at least one subform. Selecting a value in the SELECT Municipality control then passes that value to the field MUNC which appears on every subform. This works correctly until you try and enter a new municipality. Then you have to enter the information in the first subform (General), exit the application, and then select the newly entered value to have all of the forms allow data entry to the correct record.

By the way you did an excellent job explaining this method of passing info between forms - kudos