Opening a Form to a Specific Record

fat controller

Slightly round the bend..
Local time
Today, 11:24
Joined
Apr 14, 2011
Messages
758
I have a table that has two forms linked to it - one is the Main Form which is used to input the data for the bulk of entries, however on some entries there will be some additional data required which the other form (lets call it Time Form) takes care of.

What I would like to do is have a button on the Main Form that opens the Time Form, and then go to the specific record that is open on the Main Form;

I have an ID field (Autonumber + Primary Key) which displays in a text box on the main form, and it would provide the number of the record for the second form to open.

Is this possible, and if so, how please?

Thanks in advance.
 
That is quite simple. You need to pass in to the form being opened which record you would like it to be preselected on. Perhaps use OpenArgs, perhaps from the form opening reach out to some other form and have the opening form look up the specified record, etc...

Once you have determined which record you would like the UI to have pre-selected, then use this code:

Code:
  Dim daoRS As DAO.Recordset

  'Locate the record if we received a valid ID
  If lngRecordID > 0 Then
    Set daoRS = Me.RecordsetClone
    daoRS.FindFirst ("[id] = " & lngRecordID)
    'If we could find the newly added record, jump to it
    If Not daoRS.NoMatch Then
      Me.Bookmark = daoRS.Bookmark
    End If
  End If

  'Clean up the connection to the database
  Set daoRS = Nothing
So in this example I have an [id] column in the table which is a LongInt data type. The code sample will attempt to locate the record based on the unique [id] value.
 
mdlueck's code will keep the form open to 'all' records but jump to the one you want. pbaldy's will open the form just with the currently relevant information by filtering results.

Which you need will depend on... which you need, but it sounds like you may want pbaldy's, especially if you're envisioning this as a popup form.
 
@mdlueck - is there any particular benefit to using the longer code you posted?

Guaranteed not to have any residual side-effects from the WHERE clause having been passed into the Form, Sir Topham Hatt. It is merely pre-locating a record if found. I believe the WHERE clause method would actually filter / limit the number of records displayed while the WHERE clause is active. You did not state that you wanted other records filtered out.
 
Well, I just couldn't go to bed so close to seeing it work, so I have put a button on the main form with pbaldy's code, and it works perfectly.

Thanks again for the help. I am learning, little by little...... :)
 
Happy to help! If you wanted all records still available, there's a link at the bottom of the page I linked earlier that uses code similar to Michael's.
 

Users who are viewing this thread

Back
Top Bottom