Find Record for Subform (1 Viewer)

Malcolm17

Member
Local time
Today, 00:44
Joined
Jun 11, 2018
Messages
107
Hey All,

I'm looking to click a button on a continuous subform that will select that record (ID) and then display (use subform.visible = true) and then hide the subform where I have just clicked the button, I'm ok with most of the code but I am having issues with finding a code that will allows me to select the record to show in the second subform.

Please can you give me a code I can use for this, I've been trying to make something like this work:

Code:
Docmd.gotocontrol (subform)
Docmd.gotorecord,,acNewRec

Ideally I'm thinking I would use something like:

Code:
DoCmd.GoToRecord,,Forms.[Mainform]![Subform]![ID]

Malcolm
 

Malcolm17

Member
Local time
Today, 00:44
Joined
Jun 11, 2018
Messages
107
Hey Paul,

I'm not making much sense with that code, when I run it I get an error on the following line:

Code:
  .FindFirst "RoomArea = " & lngPK

Error is:
Runtime error 3464
Data type mismatch in criteria expresson

I have tried to use the text line too and it returns the no record message, but the record is there.

The full code, I have just changed slightly to fit my fields is:
Code:
'set variable to current record ID
lngPK = Me.RoomArea

Forms.Maintenance.MaintenanceAddRecord.Requery
'return form to original record
With Me.RecordsetClone
  .FindFirst "RoomArea = " & lngPK
  'if your key field is text use this line instead
  '.FindFirst "RoomArea = " & Chr(34) & lngPK & Chr(34)
  If .NoMatch Then 'just in case another user deleted it in the interim
    MsgBox "Record not found!", vbCritical
  Else 'go to that record
    Me.Bookmark = .Bookmark
  End If
End With
 

Pat Hartman

Super Moderator
Staff member
Local time
Yesterday, 20:44
Joined
Feb 19, 2002
Messages
42,981
When you want to control one subform from another, use a query as the recordsource for the subform that references the first subform.

Select ... From ... Where Somefield = Forms!mainform.Form!subform1!Somefield

Then in the Current event of the first subform, requery the second subform.

Me.Parentsubform2.Requery
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:44
Joined
Aug 30, 2003
Messages
36,118
You don't need the requery for this, that's what the link was doing. All uses of Me need to be changed to the subform reference except the one setting the variable. The variable should be declared as String if the field is text.
 

Malcolm17

Member
Local time
Today, 00:44
Joined
Jun 11, 2018
Messages
107
I've played around with this all day and have tried various scenarios and can't quite see what I am missing. I have attached a pared down example of my front end, please can you have a look at it for me?

When you open it, open the maintenance form, select a Room\Area, eg 111, the idea is to click on the edit button (this is where I am stuck) on the right to see that record, however I just cannot work out how to select that record to show in the form. Click on add does add a new record as I need it to, I'm looking to use the same subform for add and edits. To Exit either click on close or delete buttons - I haven't finished it all yet and it is copied out of my front end so does look a bit wierd.

Many thanks in advance if you can help me,

Malcolm
 

Attachments

  • Example.accdb
    956 KB · Views: 363

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 17:44
Joined
Oct 29, 2018
Messages
21,358
I've played around with this all day and have tried various scenarios and can't quite see what I am missing. I have attached a pared down example of my front end, please can you have a look at it for me?

When you open it, open the maintenance form, select a Room\Area, eg 111, the idea is to click on the edit button (this is where I am stuck) on the right to see that record, however I just cannot work out how to select that record to show in the form. Click on add does add a new record as I need it to, I'm looking to use the same subform for add and edits. To Exit either click on close or delete buttons - I haven't finished it all yet and it is copied out of my front end so does look a bit wierd.

Many thanks in advance if you can help me,

Malcolm
Hi Malcom. Please pardon me for jumping in, but I just gave it a quick try. This is just one possible way, there are others. At the end of the code for your Edit button, I added the following line.

Code:
DoCmd.BrowseTo acBrowseToForm, "MaintenanceAddRecord", "Maintenance.MaintenanceAddRecord", "ID=" & Me.ID
Hope it helps...
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 17:44
Joined
Aug 30, 2003
Messages
36,118
Is this what you're after?
Code:
  Dim lngPK  As String 'change data type if yours is different

  'set variable to current record ID
  lngPK = Me.RoomArea

  'return form to original record
  With Forms!Maintenance.MaintenanceAddRecord.Form.RecordsetClone
    .FindFirst "ID  = " & lngPK
    'if your key field is text use this line instead
    '.FindFirst "RoomArea  = " & Chr(34) & lngPK & Chr(34)
    If .NoMatch Then 'just in case another user deleted it in the interim
      MsgBox "Record not found!", vbCritical
    Else 'go to that record
      Forms!Maintenance.MaintenanceAddRecord.Form.Bookmark = .Bookmark
    End If
  End With
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 08:44
Joined
May 7, 2009
Messages
19,169
see these changes.
 

Attachments

  • Example.zip
    96.8 KB · Views: 344

Users who are viewing this thread

Top Bottom