need one form to open based on the record of another form

[gcrutch]

First I have a lookup form called AddressLookup. Then I have a form called Services and I also have a form called ServiceEvents.
Every Address will have a service. and for every service there is an ServiceEvent.

The user will go to the AddressLookup form and search for an address. Once the address is found (list box) the user will double click the address from the list box and on the double click event , if the customer already have a service the Service form will open and prefill with the information for that service. if not it will be in data entry mode and the user will enter a new service. This is based on the AddressID field in the Address table. AddressID is a foreign key in the Services table and the ServiceEvent table.

Afte the user completes the Service then they will click a button to complete the ServiceEvent.

If the customer already have a service, when the user clicks the ServiceEvent buttom the record for the ServiceEvent will be populated based on the AddressID.

Now the problem I'm having is....if the customer does not have a service and the user has to enter a NEW service, once the user enters the service and then clicks the ServiceEvent button the record will not populate like it did if the customer already have a service.

it's like the serviceEvent form is not recognizing the record if the user has to enter a new record.

here is the code i have that runs if the customer already have a service.
this is on the ServiceEvent Click event on the Service form.

Private Sub cmdEvent_Click()
On Error GoTo Err_cmdEvent_Click
Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "Service Event", , , "[AddressID] = " & Me![AddressID]



Exit_cmdEvent_Click:
Exit Sub
Err_cmdEvent_Click:
MsgBox Err.Description
Resume Exit_cmdEvent_Click
End Sub

I hope this makes sense...

 

[chrisguk]

Private Sub cmdEvent_Click()
On Error GoTo Err_cmdEvent_Click
Dim stDocName As String
Dim stLinkCriteria As String

DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "Service Event", , , "[AddressID] = " & Me![AddressID]

You may want to add this line instead. I will say that there is really no need to make your own form close and open function. The wizard provides and adequate solution. Also the forms save automatically on close anyway:

Use the revised solution below:

Private Sub cmdEvent_Click()
On Error GoTo Err_cmdEvent_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    stDocName = "[COLOR=red]YOUR FORM NAME YOU WANT TO OPEN[/COLOR]"
    
    stLinkCriteria = "[AddressID]=" & Me![AddressID]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
    Forms![COLOR=red]YOUR FORM NAME YOU WANT TO OPEN[/COLOR]!AddressID = Me.AddressID
 
Exit_cmdEvent_Click:
    Exit Sub
Err_cmdEvent_Click:
    MsgBox "Sorry there has been an error with this function, please report it to your administrator!", vbCritical = vbOKOnly, "Database Error"
    Resume Exit_cmdEvent_Click
    
End Sub
 
 
Exit_cmdEvent_Click:
Exit Sub
Err_cmdEvent_Click:
MsgBox Err.Description
Resume Exit_cmdEvent_Click
End Sub

 

[gcrutch]

thank you so much!!!!!! That worked! I love this site!

 

[gcrutch]

curious....how come that works for one form but doesn't work for another. I am trying to do the same thing but opening a different form.