select from list box populate to unbound fields on form

[mychalB]

I am trying to on event click in a listbox to populate the fields on my form. I have made each field unbound and I am trying to use this code.

Private Sub lstLabName_Click()
Set rs = db.OpenRecordset("SELECT * FROM tblLabratory WHERE tblLabratory.LabratoryID = '" & Me.lstLabName & "'&quot

rs.MoveFirst

Me.txtLabID.Text = rs("LabratoryID&quot
Me.txtLabratoryName.Text = rs("LabratoryName&quot
Me.txtContactName.Text = rs("LabContactName&quot
Me.txtContactPhone.Text = rs("LabContactPhone&quot
Me.txtAddress.Text = rs("LabAddress&quot
Me.txtCity.Text = rs("LabCity&quot
Me.cboStateProv.Text = rs("LabState&quot
Me.txtZip.Text = rs("LabZip&quot
Me.txtNotes.Text = rs("LabNotes&quot

rs.Close


End Sub

I get this runtime error "3464"

datatype mismatch in criteria expression

The LabID is an autonumber in the database. The listbox query's the table and populates LabID and Lab Name. When I run the code the me.lstLabName = LabID then produces the error.

Any suggestions?

thx in advance

Mychal

 

[BoxHead]

Mychal,

I'm not sure what you're trying to do. Opening a recordset for this operation doesn't really seem appropriate to me.

Have you considered using the DLookUp Function for the unbound controls? Or, maybe think about using a bound form with bound controls, and when you create your listbox, turn the wizard on so you can have the form display all of the records for the selected list item.
John

Use what you have,
Learn what you can,
Create what you need.

 

[mychalB]

Hello Jon

thanks for the reply.

well I originally started out with bound controls. Problem that I was having when I wanted to add a new record, I used e.g txtLab = "" for each textbox to clear the current information. What I didnt realize since it was a bound control it also removed the data from the fields in the database.

If there is a work around for this I would like to know. I prefer to use dao operations than docmds.

thanks

 

[BoxHead]

mychal,

There's no reason to "clear" the controls.

Just to see what is available, take four minutes and do the following.

From the Database window, go to the Forms tab and select New.
On the New Form window that opens, Select your table from the dropdown and select "Autoform: Columnar".
Click 'OK'.

The form that is created will contain controls for all of the fields from your table.
At the bottom of the form is a navigation bar with little arrows pointing forward and backward to move from one record to the next. The arrow with the asterisk next to it will take you to a blank instance of the form to add a new record.

Understand that the form is not separate from the table it is bound to. It is merely a different display of the data. If you set txtLab = "", you are not changing the control, you are changing the data.

I may be misreading this (and I apologize if that's the case), but it seems you're trying to use some rather advanced coding to perform some simple, built-in operations. My guess would be that you have training and/or experience in other development applications. Access makes this sort of thing pretty simple. Don't worry, it'll get complicated enough as you get further into it.


HTH
John

Use what you have,
Learn what you can,
Create what you need.