How to Link forms with VBA code 1

[Lavenderchan]

Hi I have a form that I'm using for work in access 2007. I enter a task on the main form and then it opens a form to enter the rest of the required information but I unsure on how to link those forms together so the record is in sink with both forms.

Thanks,
Keri

 

[Remou]

The OpenForm action has a Where argument:

DoCmd.OpenForm "FormName",,,"ID=" & Me.ID

If you wish to have both forms open and synched, you may wish to use the Current Event. The easiest way, however, is with a form - subform set-up.

http://lessthandot.com

 

[Lavenderchan]

I've used this code but its giving me errors. Did I do it wrong.

Private Sub Form_Current()
If Me.cboTask = ("Excess Income Report") Then
DoCmd.OpenForm "Excess Income Details", , , "[Tracking Log ID]=" & Me.ID
End If
If Me.cboTask = ("Reserve For Replacement") Then
DoCmd.OpenForm "Reserve for Replacement Details", , , "Tracking Log ID=" & Me.ID
End If
End Sub

 

[Remou]

Does your form have a field or control called ID that matches to [Tracking Log ID]?

http://lessthandot.com

 

[Lavenderchan]

I have the primary key ID. How do you know if it matches?

 

[PHV]

its giving me errors
Which error message(s) ?
If the ID isn't defined as numeric, you may try this:

DoCmd.OpenForm "Excess Income Details", , , "[Tracking Log ID]='" & Me!ID & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Lavenderchan]

I'm unable to get the form to trigger now. Is there something I missed?

thanks for your help.

 

[Remou]

Put a break in the code and see if the current event is firing. Do the forms open if you leave out the Where statement?

http://lessthandot.com

 

[Lavenderchan]

the forms open when I enter the task I have an after update event there to trigger the forms. But its not in sink with the form I enter the first part of the data on.

 

[Remou]

I didn't quite catch that. The code you posted above is for the Current event, where does the after update event come in?

Have you tried substituting an actual value? For example:


[tt]DoCmd.OpenForm "Excess Income Details", , , "[Tracking Log ID]=" & 10[/tt]

http://lessthandot.com

 

[Lavenderchan]

I think I stated how I wanted this to work. I have a macro on another form if you click it by the Tracking Log ID number it opens to that record in the current form. So I tried converting it to use in on the after update event to see it would work that way. I just want to be able to automate my data entry. When I converted the marco and add it to the event its giving me an error
message "missing opertor in query expession {id] =1.
Private Sub Assigned_To_AfterUpdate()
If Me.cboTask = ("Reserve for Replacement") Then
DoCmd.OpenForm "Reserve for Replacement Details", acNormal, "", "[Tracking Log ID]=" & Nz([Tracking Log ID], 0), , acDialog
If (Not IsNull([Tracking Log ID])) Then
TempVars.Add "CurrentID", "[Tracking Log ID]"
End If
If (IsNull(ID)) Then
TempVars.Add "CurrentID", "Nz(DMax(""[Tracking Log ID]"",[Form].[RecordSource]),0)"
End If
DoCmd.Requery ""
DoCmd.SearchForRecord , "", acFirst, "[Tracking Log ID]=" & TempVars!CurrentID
TempVars.Remove "CurrentID"

End If
any help would be greatly appreactated.

Keri

 

[Remou]

So the ID does not exists yet, is that correct? If so, you would be better of with openargs, another argument of the OpenForm action. The code on the form that is opened can refer to the OpenArg property.

You may have referred to how you wish this to work, but perhaps not all of you ideas were in this post.

http://lessthandot.com

 

[Lavenderchan]

Thank you so much for your help. Yes the Id doesn't exits yet on the form I'm opening. How do I refer the open arg property.
Keri

 

[Remou]

[tt]DoCmd.OpenForm "FormName",,,,acFormAdd,,Me.ID[/tt]

The opened form would also need some code:

If Me.Openargs<>vbNullstring Then
   Me.Id = Me.Openargs
End If

However, this is beginning to look quite messy, with a lot of opening and closing forms, which can get quite annoying when you are doing data entry.

http://lessthandot.com

 

[Lavenderchan]

I got an error message runtime error 2505 an expression in agurement 6 has an invaild value.

Private Sub Assigned_To_AfterUpdate()
If Me.OpenArgs <> vbNullString Then
Me.ID = Me.OpenArgs
End If
If Me.cboTask = ("Reserve for Replacement") Then
DoCmd.OpenForm "Reserve for Replacement Details", , , acFormAdd, , Me.ID
If (Not IsNull([ID])) Then
TempVars.Add "CurrentID", "[ID]"
End If
If (IsNull(ID)) Then
TempVars.Add "CurrentID", "Nz(DMax(""[ID]"",[Form].[RecordSource]),0)"
End If
DoCmd.Requery ""
DoCmd.SearchForRecord , "", acFirst, "[ID]=" & TempVars!CurrentID
TempVars.Remove "CurrentID"
End If
I think I getting the error because the acformadd is =0 when I run the debugger and everthing else is set to the id. I'm not sure how to trouble shoot this.
thanks,
Keri

 

[Remou]

You test the open args on the form you are opening, not the current form.

http://lessthandot.com

 

[Lavenderchan]

Hi thanks, I added the open args to the on open event on the form I opening and now I getting this error message Rutime error "-2147352567 (80020009)! You can't assign a value to this object.

Any ideas of what could be cause this?
thanks,
Keri

 

[Lavenderchan]

Ok I've got one form opening where it needs to be. I just have to figure out why the other one isn't. Thanks, for you help.

 

[Lavenderchan]

Hi i have the event working for my first form but not my second form. Why does it like the open args for the second form. I'm not understanding why.
here are the codes

Private Sub Assigned_To_AfterUpdate()
If Me.cboTask = ("Excess Income Report") Then
DoCmd.OpenForm "Excess Income Details", , , "ID=" & "ID"
End If
If Me.cboTask = ("Reserve for Replacement") Then
DoCmd.OpenForm "Reserve for Replacement Details", , , "ID=" & "ID"
End If
End Sub
Its working for the excess income details report but not the reserve for replacement details form.

Any insight would be help ful.
Thanks,
keri

Private Sub Form_Open(Cancel As Integer)
If Me.OpenArgs <> vbNullString Then
Me.ID = Me.OpenArgs
End If
End Sub

 

[Remou]

I can only guess that it is working on one form by accident, because what you are passing to OpenArgs is the word ID, not a value for the ID.

[tt]DoCmd.OpenForm "Reserve for Replacement Details", , , "ID=" & Me!ID[/tt]

Me = a shortcut name for the current form
ID = the name of the field or control on the current form that holds the ID. If you set a break point, you can check what os passed to OpenArgs.

http://lessthandot.com