SQL Select statement problem

[marky_dp]

Hi, I have the following Select statement where basically i want to retrieve a customer's first and last name depending on the customer id that is already shown on my form (the customer id on the form is being displayed in a text box, CustomerIDText):

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

Dim mySQL As String

mySQL = "SELECT CustomerID, CustomerFirstName, CustomerLastName"
mySQL = mySQL + " FROM CustomerTBL"
mySQL = mySQL + " WHERE CustomerID = '" & Me.CustomerIDText.Value & "' "

myRecordSet.Open mySQL

But when i go to open my form I get a run time error "Data type mismatch in criteria expression"

I think the part of my statement '" & Me.CustomerIDText.Value & "' is the problem, not sure why though. Any help would be appreciated, cheers.

Also once i've got my sql statement to work, how would i get an unbound textfield to display the first name, for example that i have retrived, would it be something like: textfield.value = mySQL FirstName ?

 

[The Stoat]

Hi, I have the following Select statement where basically i want to retrieve a customer's first and last name depending on the customer id that is already shown on my form (the customer id on the form is being displayed in a text box, CustomerIDText):

I suspect that your customer ID is not a String and therefore you don't need the single quotes around '" & Me.CustomerIDText.Value & "'


HTH

TS

 

[neileg]

Why have you used 'Value' in this statement Me.CustomerIDText.Value?

 

[marky_dp]

Cheers Stoat, it was the single quotes, not needed at all. Now all I need to do is figure out how to make the value of a text field contain the first name that wa pulled back from my sql statement.

cheers again

 

[The Stoat]

Cheers Stoat, it was the single quotes, not needed at all. Now all I need to do is figure out how to make the value of a text field contain the first name that wa pulled back from my sql statement.

cheers again

CustomerTBL should only have 1 record per customer shouldn't it?

 

[marky_dp]

yeah, the customer table does have only one record per customer.

what i want to do on my form, which already displays a customers id, is also display their first and last name in seperate text fields. Hence pulling the id, first name and last name back in a sql statement where the id in the table is equal to the id on the form.

 

[The Stoat]

yeah, the customer table does have only one record per customer.

what i want to do on my form, which already displays a customers id, is also display their first and last name in seperate text fields. Hence pulling the id, first name and last name back in a sql statement where the id in the table is equal to the id on the form.

This link shows you how to bind a form to the recordset


http://support.microsoft.com/kb/281998

or you could return the data from the recordset to the form.

Me.TXTBOXCustomerID = Rst.Fields.Item(0) '0 is the first field returned in the recordset
Me.TXTBOXCustomerFirstName = Rst.Fields.Item(1)

or

Me.TXTBOXCustomerID = Rst.Fields.Item("CustomerID")
Me.TXTBOXCustomerFirstName = Rst.Fields.Item("CustomerFirstName")

HTH

TS

 

[marky_dp]

cheers the stoat, you have come up trumps again.

 

[The Stoat]

cheers the stoat, you have come up trumps again.

No worries.