Combobox Autoexpand Question (1 Viewer)

[gswan]

Here are the applicable portions of my database design –

tblMembers.MemberID (Primary Key)
tblMembers.AddressID (foreign Key – Addresses table)
tblMembers.FirstName
tblMembers.LastName
tblMembers.SponsorID

tblAddresses.AddressID (Primary Key)

queryMembersAddressIDs.MemberID (from tblMembers)
queryMembersAddressIDs.AddressID (from tblAddresses)
queryMembersAddressIDs.FullName (which is - [tblMembers].[LastName] & “, “ & [tblMembers].[FirstName] )


I am trying to setup a combo-box field on a form that will allow me to use the auto-expand feature. I want to use the auto-expand feature so I can type the members name (as shown in qryMembersAddressIDs.FullName), and then store that members AddressID in the tblMembers.SponsorID field.

I thought one way to do it would be to make 2 combo-box fields on the form, one with the member’s full name (for the lookup) and another with the members AddressID (to store in the tblMembers.SponsorID field). I thought there would be a way to link the two combo-boxes. I found a lot of information about linking cascading combo-boxes, but I don’t need cascading combo-boxes.

How do I do this?

 

[John Big Booty]

Have a look at the push back functionality in the sample posted here, that should give you some pointers.

 

[gswan]

I see that the Post Code Combobox and Suburb text field are connected the way I need. But there's a lot of code in there! What part of the code populates the Suburb text field when a Post Code is selected? That's the only piece I'm looking for.

 

[John Big Booty]

Have a look at the After Update event of Text8 (the Postcode text box). Read the comments, in Green, and examine the code.

 

[gswan]

I think I see - it's this part, right?

Private Sub Form_Current()

'set the Row source for the suburbs combo and requery and populate the postcode text box

Me.Combo6.RowSource = "SELECT TBL_Pcode.PCodeID, TBL_Pcode.Locality, TBL_Pcode.Pcode, TBL_States.StateID " & _
"FROM TBL_States INNER JOIN TBL_Pcode ON TBL_States.StateID = TBL_Pcode.StateID " & _
"WHERE (((TBL_States.StateID)=[forms]![FRM_Address]![combo0])) " & _
"ORDER BY TBL_Pcode.Locality;"
Me.Combo6.Requery

Me.Text8 = Me.Combo6.Column(2)

End Sub

 

[John Big Booty]

Which bit are you currently trying to get working?

 

[gswan]

I'm trying to get tblMembers.AddressID to update with the AddressID of the Member I choose in the Lookup/Combobox. Here's the code I've got in my Form.

Option Compare Database

Private Sub Text14_AfterUpdate()

End Sub

Private Sub Combo1_Change()

'Force Members combo to drop down when user starts typing

If Me.Combo1.SelStart = 1 Then
Me.Combo1.Dropdown
End If

Me.TestSponsorAddrID = Me.Combo1.Column(2)

End Sub

Private Sub Form_Current()

'set the Row source for the suburbs combo and requery and populate the postcode text box

Me.Combo1.RowSource = "SELECT qryMembersAddressIDs.FullName, qryMembersAddressIDs.AddressID, tblMembers.MemberID" & _
"FROM qryMembersAddressIDs INNER JOIN tblMembers ON qryMembersAddressIDs.MemberID = tblMembers.MemberID" & _
"WHERE (((tblMembers.MemberID)=[Forms]![frmTestMembers]![Combo1])) " & _
"ORDER BY qryMembersAddressIDs.FullName;"

Me.Combo1.Requery

Me.TestSponsorAddrID = Me.Combo1.Column(2)

End Sub

 

[John Big Booty]

I'm having a bit of trouble following as you have three lots of code there. In future please use the Code tag when posting code (that's the Hash "#" button at the top of the posting window) and post post the different pieces of code using discrete Code Tags. You've also not explained what's happening and how that differs from what you are expecting.

 

[gswan]

Sorry about that John. I'm pretty new to Access and VERY new to Visual Basic. It took me quite a while to figure this out, but I believe I've got it. I'm posting the sample DB just in case it might help another user in the future. Thanks for you help!