Problem when opening form

technohead · Apr 28, 2009

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 · Apr 28, 2009

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 · Apr 28, 2009

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 · Apr 28, 2009

Are both forms based on the same Table?

technohead · Apr 28, 2009

No, they are based on differeent tables.

technohead · Apr 29, 2009

surely there is a way of doing this?

Rabbie · Apr 29, 2009

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 · Apr 29, 2009

Rabbie said:
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 · Apr 29, 2009

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

In first form

Code:

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

Code:

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 · Apr 29, 2009

Absoloute legend missinglinq, worked a treat!!!

Thanks for everyones help!

Problem when opening form

technohead

New member

Uncle Gizmo

Nifty Access Guy

technohead

New member

Uncle Gizmo

Nifty Access Guy

technohead

New member

technohead

New member

Rabbie

Super Moderator

technohead

New member

missinglinq

AWF VIP

technohead

New member

Similar threads

Users who are viewing this thread