Seperating a combo box into text boxes

[Danielle17]

I have a combo box which contains a Hotel name, street address, city, state, zip and the auto number that it is assigned when it is first entered into the database. I have a form which allows a user to choose a customer which then pops up a 'prefered' hotel. This is displayed in 3 text boxes: Hotel Name, Hotel Address, and Phone#. I also have a combo box right under those 3 text boxes that allows the user to choose a different hotel if the 'Prefered'one is not the one they want. I have some of the code for the hotel addresses combo box but it's not working right:

Private Sub cbHotelInfo_Click()
Dim db As Database
Dim qdfHotels As QueryDef
Dim rsHotels As Recordset
Dim nHotelsID As Integer

Set db = CurrentDb
Set rsHotels = db.OpenRecordset("Hotels&quot
Set qdfHotels = CurrentDb.CreateQueryDef("&quot

rsHotels("ID&quot = Val(Mid(Me.cbHotelInfo, InStr(1, Me.cbHotelInfo, "-- ", vbTextCompare) + 2))

'the line above is extracting the autonumber from the end of the string in the combo box so that it can be used to run a query to find the matching autonumber in the table. That way I can pull the info that matches the number out of the table and put it into the text boxes on the form.

nHotelsID = Val(Mid(Me.cbHotelInfo, InStr(1, Me.cbHotelInfo, "-- ", vbTextCompare) + 2))

qdfHotels.SQL = "SELECT * FROM Hotels WHERE ID = '" & nHotelsID & "';"
Set rsHotels = qdfHotels.OpenRecordset

If Not rsHotels.EOF And Not rsHotels.BOF Then
Me.txtHotel = rsHotels.Fields("HotelName&quot
Me.txtHotelAddr = rsHotels.Fields("HotelAddr&quot & " (" & rsHotels.Fields("HotelCity&quot _
& ", " & rsHotels.Fields("HotelState&quot & " " & rsHotels.Fields("HotelZip&quot & &quot"
Me.txtHotelPhone = Format(rsHotels.Fields("HotelPhone&quot, "###-###-####&quot
End If

rsHotels.Close
qdfHotels.Close

End Sub

I hope someone can tell me what the problem is....

 

[nickjar2]

i have only had a quick look so far but 1 thing i noticed is that you will need either:

rshotels.edit OR rsHotels.addnew

rsHotels("ID&quot = Val(Mid(Me.cbHotelInfo, InStr(1, Me.cbHotelInfo, "-- ", vbTextCompare) + 2))

Nick

 

[braindead2]

if i understand properly, what you are doing is running a
query to fill the text boxes with info already in the combobox. Correct?
to display what is in the combobox place this in the text box instead

=[cbHotelInfo].[column](0) 'to display first column
=[cbHotelInfo].[column](1) 'to display second column

you will want to refresh after the update on the combobox
but it will show what is select in the combo boxes

If I misunderstand I'm sorry.