combo 1 selects records for combo2

[jadams0173]

I have 2 combo boxes where combo one determines what records are shown in combo 2. I have this working, but when the user selects the value from combo 2, I'm having difficulty getting the rest of the form to populate with the selected info for that record. Here is the after update event for combo 1 where I change the rowsource.

Private Sub Customer_AfterUpdate()

StencilPartNumber.RowSource = "SELECT tblStencils.Customer, tblStencils.StencilPartNumber, tblStencils.StencilRev, tblStencils.Side, tblStencils.EngineerResponsible, tblStencils.SLRPartNumber, tblStencils.StorageLoc, tblStencils.Comments " _
& "FROM tblStencils WHERE (((tblStencils.Customer)=[forms]![frmfindstencils]![customer]));"

End Sub

What if anything needs to go in the Afterupdate for combo 2 to make the form populate. I read the FAQ on this but still couldn't get it to work. Thanks for the help!!

 

[Miquella]

I believe that you would just need to put code in the AfterUpdate function for combo2 to Refresh/Requery your form, or manually populate your controls.

Without further information, I'm not sure what to tell you?

-Bean
"Everything should be made as simple as possible, but not simpler." -Albert Einstein

 

[jadams0173]

I'll try the the refresh/requery of combo2. What other information do you need? I'll provide as much as I can.

 

[Miquella]

No, not a refresh/requery of your combo2, you need to refresh/requery your other controls that you're trying to re-populate.

Other information such as what controls you're trying to re-populate, and what data they are supposed to contain.

-Bean
"Everything should be made as simple as possible, but not simpler." -Albert Einstein

 

[Lamprey79]

If I understand you correctly, you've got your combo2 populated correctly, except that clicking it doesn't find the record it's linked to on a form?

If that's the case, try this in AfterUpdate of combo2:

=======================================

Dim rs As Object
Dim dbs As Database

Set dbs = CurrentDb()
Set rs = Me.Recordset.Clone

rs.FindFirst "[whichever data is selected in combo2] = " & CStr(Me![combo2])
Me.Bookmark = rs.Bookmark

=======================================

Alternately, you can just change the RecordSource property of the form you're trying to populate programmatically; this is done much the same way you're doing it with populating combo2. Also in combo2's AfterUpdate, and assuming storage location is what's selected in combo2:

Dim strStorageLocation as String

strStorageLocation = combo2.Value
Form_frmWhatever.RecordSource = "SELECT tblBlah.Field1, tblBlah.field2 FROM tblBlah WHERE ((tblBlah.StorageLoc = '" & strStorageLocation & "'));"




Hope this helps.

 

[jadams0173]

Thanks to both fbean and Lamprey79 for helping. I will try to provide more info...ok here goes. I've shortened the field names.

tblOne has the following:
Field datatype

ID Autonumber pk
SPN text
SR text
Customer text
side text
ER text
SLRPN text
date date/time
SL text
comments memo
NotNeeded yes/no
flag Number

on the form (frmfindstencils)there are 2 combo boxes and 6 txtboxes
combo1 (customer)bound to Customer--This selection determines the Rowsource for combo2.

combo2(SPN) bound to SPN--Displays only the SPN's for the selected Customer

After the selection of combo2 i would like the remaining 6 txtboxes that are each bound to the table to populate with the information for that record.

Option Compare Database

Private Sub Customer_AfterUpdate()

SPN.RowSource = "SELECT tblStencils.Customer, tblStencils.SPN, tblStencils.SR, tblStencils.Side, tblStencils.ER, tblStencils.SLRPN, tblStencils.SL, tblStencils.Comments " _
& "FROM tblStencils WHERE (((tblStencils.Customer)=[forms]![frmfindstencils]![customer]));"

End Sub

Private Sub StencilPartNumber_AfterUpdate()
Me.Requery
End Sub

Here's how I've tried to do it.

 

[PHV]

What is the RecordSource property of your form ?
The basic idea is to dynamically reset it in the StencilPartNumber_AfterUpdate event procedure:
Private Sub StencilPartNumber_AfterUpdate()
Me.RecordSource = "SELECT ... FROM tblStencils" _
& " WHERE Customer='" & StencilPartNumber.Column(0) & "' AND StencilPartNumber='" & StencilPartNumber.Column(1) & "'"
End Sub

I assume your 2 combos are unbound ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244