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]

A couple of things you might consider.

Just putting the job id into the first records job id does not find the record you seek. You either have to set the filter and set filter on, or use recordsetclone bookmark to go to the record.

Instead of using passing the id as an argument, what if you modified the details query using the job form.job id as criteria. You may also have to use job form.job id as the default value for detail's job id field to ensure if it is a new record, it is assigned to the right job. Use the expression builder to select the form and control you want to use. Expression builder is the button with "..." on it.
This will pull the value off the job form.

 

[antonyx]

ok.. let me just say that the code was not written by me, it was generated with the button wizard.

what if you modified the details query

i actually dont have a query involved at the moment. just 2 tables and 2 forms built for the tables using the wizard.

job table
job id (pk)
job date

job pickup table
job pickup id (pk)
job id (fk)
pickup address

so if it is possible, rather then suggesting another method, would it be at all possible to modify that code above, or to explain in a bit more detail (like you were explaining something to a monkey) why my current method isnt working, and what i can do to fix it.

thanks in advance for your patience.

 

[spizotfl]

you could drop the open args part and then on the second forms open event add a line like:
me.[job id] = forms!firstFormName.[job id]
or
me.[job id].DefaultValue = forms!firstFormName.[job id]
depending on what suits your fancy...

 

[antonyx]

ok now when i click my button on my job form i get this error

http://bb.1asphost.com/antonyx6/error1.jpg

and with this code in my job pickup form

Option Compare Database

Private Sub Form_Open(Cancel As Integer)
Me.[job id] = Forms!job.[job id]
End Sub

i get this error when tryin to view it in form view.

http://bb.1asphost.com/antonyx6/error1.jpg

 

[antonyx]

that error 2 picture refers to when i view the job pickup form.. not the job form. the job forms opens fine.

 

[antonyx]

sorry, the second url should be


http://bb.1asphost.com/antonyx6/error2.jpg

 

[spizotfl]

try it on the load event, i think the problem is that the controls and data aren't available yet in the open event.

 

[antonyx]

yes that did it, thank you..

now will i have any problems with this method in the future..

take this example..

the user is viewin the job form.. then click the button to add a pickup.. so if this is a new job, say the id is 20.. when the button is pressed then id20 will be passed to the job pickup form. if they do not wish to add a pickup tho, will the id still remain in my job pickup table.. will it save.. if i create a button that simply closes the form and not save it, will that solve this problem.

 

[stix4t2]

I don't think your a monkey. spizotfl's code has the same problem as your code. The recordset for the detail form has all the records, and when it opens it shows the first record. By setting the Job ID field to a value, only modifies the first record, and maybe corrupting your data.
You want to use the form_load event, because the data has not been loaded in the form_open event.

I saw some code on another thread and used part of it here

set rs = me.recordsetclone
rs.findfirst "[Job ID] = " & forms!yourfrmJobName![Job ID]
if( rs.nomatch ) then
docmd.GoToRecord acDataForm,me.Name,acNewRec
me.[job id] = forms!yourfrmJobName![Job ID]
else
set me.bookmark = rs.bookmark
end if
rs.close

This code will find the matching record in the details form and move to that record. If the record does not exist, goto a new record and set the job id. You may also consider setting the details form's property cycle to 'current record' so not to allow moving to other records. This property is on the other tab for the form.

'get a copy of the details form recordset
set rs = me.recordsetclone
'find the matching job id
rs.findfirst "[Job ID] = " & forms!yourfrmJobName![Job ID]
'test to see if it exist
if( rs.nomatch ) then
'if no matching record, go to a new record
docmd.GoToRecord acDataForm,me.Name,acNewRec
'set the job id for the new record
me.[job id] = forms!yourfrmJobName![Job ID]
else
'if exist, move the form's bookmark to found record
set me.bookmark = rs.bookmark
end if
'close the recordset
rs.close

 

[stix4t2]

Use the [job id].defaultvalue property instead of assigning the job id, like i did in my example. The difference is that the default value will not set the value until another piece of information is entered. So closing with out change does not add the record. My previous sample will create a record and you said you didn't want that.

 

[spizotfl]

thanks for the info stix4t2. you have shown me something too.

 

[antonyx]

ok let me just clear things up. i should place the first bit of code you gave me in my job form button. and the second piece of code in the onload event of the job pickup form..

is that rite.

i think i better clear this up b4 i ask one more question regarding this system

 

[stix4t2]

The job form just opens the detail form without passing the arguments. The first code is uncommented, and the second goes through each line. Put the first code into the load event and it should be working.

 

[antonyx]

no no, my bad.. ill just put the main bit of code, which is both parts u gave me, in the onload of my job details form.

 

[antonyx]

ok. as shown i have 2 forms.. form one is 'job' based on the 'job' table

form 2 is 'job pickup' based on the 'job pickup' table.

my job form has the following code.

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 my job pickup form has the following code

Option Compare Database

Private Sub Form_Load()
Set rs = Me.RecordsetClone
rs.FindFirst "[job id] = " & Forms!job![job id]
If (rs.NoMatch) Then
    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
    Me.[job id] = Forms!job![job id]
Else
    Set Me.Bookmark = rs.Bookmark
End If
rs.Close
End Sub

i have a button 'Command6'... that opens my job pickup form from my job form. when i click it however i get the following error..

runtime error 424, object required.
why is this?

 

[stix4t2]

Drop the set on the bookmark, it must be a value and not an object, see now I am the monkey!
me.bookmark = rs.bookmark

Also remove the job id in the openform call, it is not necessary any more

DoCmd.OpenForm stDocName, , , stLinkCriteria

 

[antonyx]

yes, thank you that works.. you are not the monkey, you are the wise ape, who looks over the monkeys.

listen.. this seems to work now but i have one final requirement..

my user may wish to add several pickup addresses to the job.
therefore, when this job pickup form is opened..

if it is creating a new record with the job id, and the user enters one address, then tabs to the next record, i want that record to also have the same job id.. see what i mean.. so the user can assign multiple pickups to that job..

and also, if a job already has 2 or 3 pickups, then when the id is located and the job pickup details are loaded, the user will be able to see all of the records assigned to that job

 

[stix4t2]

This is the code you want to use, the form only contains the records related to the job id. All new records will be associated back to the job.

Option Explicit - means that all variable must be declared and is good practice to use it, it helps keep the bugs away.

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

 

[antonyx]

ok i see...basically tho my form already has this form load code..

Option Compare Database

Private Sub Form_Load()
Set rs = Me.RecordsetClone
rs.FindFirst "[job id] = " & Forms!job![job id]
If (rs.NoMatch) Then
    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
    Me.[job id] = Forms!job![job id]
Else
    Me.Bookmark = rs.Bookmark
End If
rs.Close

End Sub

where do i place that code you gave me in here..
ive tried a few ways of placing it directly in there and i get errors.