populate text boxes from a query result

[jammyp]

hi,
I hope someone can help me here..

I have a form with ID, town and address on it.
I want to beable to type in an ID, press enter then if the ID exists in a query then populate the town and address text boxes with the correct info..

If the ID doesn't exist, then open another form so the user can choose the site from the list..

At the moment I think I am going about it the wrong way :


Private Sub ID_AfterUpdate()

intResult = DCount("*", "Q_findsite", "")

If intResult = 0 Then

Dim stDocName As String

stDocName = "Sites_listbox"
DoCmd.OpenForm stDocName, ,

End If

If intResult = 1 Then

Dim rst


--THIS PART FAILS WITH A RUNTIME 3061.. ?
Set rst = CurrentDb.openrecordset("Select town,address FROM Q_findsite ")

[Forms]![AddFault]![Town] = rst.Fields(0)

[Forms]![AddFault]![Address] = rst.Fields(1)
End If

End Sub

the Q_findsite query is expecting a parameter for ID = [forms]![addfault]![ID]


Is there an easier way of doing this ?
thanking you in advance.

 

[neileg]

I think a combo box will do what you want.

 

[jammyp]

Run-time error 13.
Hi, I have modifed my code to :

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Site Data] " & _
"where [Site Reference] = " & SiteNumber)


[Forms]![AddFault]![Town] = rst(Town)

[Forms]![AddFault]![Address] = rst(Address)

but I get a runtime error, when I try to access my references I have a system registry error.
is there anyway of doing this without using recordset ?

 

[jammyp]

hi Neil, how can I use a combo box to do this ?
I don't want to have a record source for my form..

 

[jammyp]

Just to let you know , I have a lot of info on this form, not just the 3 fields I mentioned. I have various combo boxes to select other data. the only part I have problems with is typing a number into the ID textbox, then on aftre update inserting the correct values into the town and address text boxes...

 

[neileg]

A combo box is usually bound to a table or query, but this is not binding the form. There is a combo property, Limit to List. As the user types in the ID, the combo will 'home in' on matching records, you don't have to use the scroll box. If the limit to list property is set to No, then the user can type in an ID that isn't in the underlying data. Of course, you should add any new values to underlying data.

Now a combo box can have as many columns as you want. If you set the width of a column to zero, the data is still there but the user can't see it. If you add the town and address fields to the combo but hide them, you can add some code to the after update event of the combo to populate your text boxes for town and address data.

I'm pretty sure that's what you want to do.

 

[jammyp]

thanks Neil, I've played aronud with this and undderstand what you mean now.
thank you, this is now working..