By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,234 Members | 1,844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,234 IT Pros & Developers. It's quick & easy.

Find a record on a subform when clicking a row from a listbox

P: 8
Hi,
I'm trying open and find a record on a subform when clicking
on a row of a listbox on my main form,
Here is what I've got so far.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Listbox1_Click()
  2.   Dim src As Integer
  3.   Dim subfhis As String
  4.   subfhis = "HISTORY"
  5.  
  6.   With Listbox1
  7.     src = CLng(.Column(0))
  8.     DoCmd.OpenForm subfhis
  9.     DoCmd.GoToRecord acDataForm, subfhis, acGoTo, src
  10.     MsgBox src
  11.   End With
  12. End Sub
The First column of my listbox (BARCODE) is also the Foreign key on my HISTORY table which shows up in my subform.
The problem is that I can't find the correct record on my subform.
May 22 '17 #1

✓ answered by jforbes

I think you are close, there might be a shorter, easier way to accomplish what you are doing, you can open a Form and supply it a Filter OnLoad. This will limit the Form to only showing the specific record. The following is my best guess for you based on what you provided:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Listbox1_Click()
  2. Dim src As Integer
  3. Dim subfhis As String
  4. subfhis = "HISTORY"
  5.  
  6. With Listbox1
  7. src = CLng(.Column(0))
  8. DoCmd.OpenForm subfhis, , ,"BARCODE=" & src
  9. MsgBox src
  10. End With
  11. End Sub

Share this Question
Share on Google+
4 Replies


jforbes
Expert 100+
P: 1,107
I think you are close, there might be a shorter, easier way to accomplish what you are doing, you can open a Form and supply it a Filter OnLoad. This will limit the Form to only showing the specific record. The following is my best guess for you based on what you provided:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Listbox1_Click()
  2. Dim src As Integer
  3. Dim subfhis As String
  4. subfhis = "HISTORY"
  5.  
  6. With Listbox1
  7. src = CLng(.Column(0))
  8. DoCmd.OpenForm subfhis, , ,"BARCODE=" & src
  9. MsgBox src
  10. End With
  11. End Sub
May 22 '17 #2

PhilOfWalton
Expert 100+
P: 1,430
I think all you need is something like

Expand|Select|Wrap|Line Numbers
  1. Private Sub Listbox1_Click()
  2.  
  3.     SubFHis.SetFocus
  4.     SubFHis.Form!KeyToThisRecord.SetFocus
  5.     DoCmd.FindRecord ListBox1
  6.  
  7. End Sub
  8.  
KeyToThis record will probably be the primary key of the subfprm record.

Phil
May 22 '17 #3

P: 8
Thank you so much jforbes for your quick responce and
sort lesson!
It works perfectly, I will use it again for sure!
Cheers
May 22 '17 #4

P: 8
Thanks PhilOfWalton,
it does not work, i've tryed:
SubFHis.Form!BARCODE.SetFocus
but i get "424 error object required"
The primary key of the subform is an autonumber
and does not "link" to the HISTORY table of my subform.
I have a foreign key there "BARCODE" to connect it to my main table.
Also what value DoCmd.FindRecord ListBox1 will give me?
ListBox1 is a multicolumn listbox.
May 22 '17 #5

Post your reply

Sign in to post your reply or Sign up for a free account.