Problem when opening form

[technohead]

Hey I have two forms, both of related tables. Both related by VisitID field.

So when I input data into the first form I then want to input data into the related table/form, so ive created a command button which opens the other form. However it always opens the form on record number 1. I want the form to open with the visitID field matching the first form.

Does anyone know a way of doing this?

Thanks

 

[Uncle Gizmo]

If you use the command button wizard, then one of the options when creating a button to open a form is to open the form to the related record. Have a go with the command button wizard and if you run into any problems post back.

 

[technohead]

Hi thanks for your quick reply.

Ive created the button again:

Form operations - open form - (my form) - open the form to find specific data to display - (matched visitID)

However when opening the form, it still opens at record number 1 every time

 

[Uncle Gizmo]

Are both forms based on the same Table?

 

[technohead]

No, they are based on differeent tables.

 

[technohead]

surely there is a way of doing this?

 

[Rabbie]

If the second form is based on a query surely you should be ablle to set the criteria so that visitID on first form = VisitID in your query.

 

[technohead]

If the second form is based on a query surely you should be ablle to set the criteria so that visitID on first form = VisitID in your query.

Thanks, ive set up a query now.

However, the query only matches the records if there is a record in place in the related table/form. I am trying to add data into this table/form so when I click on my new button in the first form (opens query/form) if there is no related record (which will be most of the time) The VisitID sets itself to 0.

I need to be able to add a new record in the first form, then click on a button which opens the new query/form matching the VisitID to a new record in the second form.

Is this possible?

Thanks a lot for your help
Sean

 

[missinglinq]

This will do what you want, utilizing the OpenArgs argument of OpenForm

In first form

Private Sub YourNewButton_Click()
If Not IsNull(Me.VisitID) Then
  DoCmd.OpenForm "TableB", , , , , , Me.VisitID
 Else
  MsgBox "A Visit ID Must Be Entered First!"
 End If
End Sub

In the second form

Private Sub Form_Load()

If Not IsNull(Me.OpenArgs) Then
 Set rst = Me.RecordsetClone
 
 rst.FindFirst "[VisitID] = '" & Me.OpenArgs & "'"
 'rst.FindFirst "[VisitID] = " & Me.OpenArgs ' Use this for a Numeric ID
  If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   Else
    DoCmd.GoToRecord , , acNewRec
    Me.VisitID = Me.OpenArgs
   End If

rst.Close
Set rst = Nothing
End If

End Sub

Just substitue your actual form name for FormB.

This will go to the corresponding record in the second form or, if there is no matching record, goto a new record and fill in the VisitID number you entered on the first form.

Notice the commented line

'rst.FindFirst "[VisitID] = " & Me.OpenArgs ' Use this for a Numeric ID

This needs to be substituted for the previous line if VisitID is Numeric instead of Text.

 

[technohead]

Absoloute legend missinglinq, worked a treat!!!

Thanks for everyones help!