A little Help would be nice

[maximo1970]

Got this code, but getting an error when I call the Function within a form.

Run-Time error `3075`:

Syntax error (missing operator) in query expression `Customer = John Smith`.

The code stop's at : Set RS = DB.OpenRecordset(sSQL)

I've been looking at it for most of the morning and can't see where the problem is. So any pointers.

Cheers


Function UpdateCustomers() As String
Dim sSQL As String
Dim DB As Database
Dim RS As Recordset

sSQL = "SELECT * FROM tbl_Customer_Details " & _
"WHERE Customer = " & [Forms]![frm_Allocate_VLAN]![Customer]
Set DB = CurrentDb
Set RS = DB.OpenRecordset(sSQL)

If Not RS.EOF Then
RS.MoveLast
RS.MoveFirst
End If

If RS.RecordCount < 1 Then
sSQL = &quot;INSERT INTO [tbl_Customer_Details] VALUES (&quot; & [Forms]![frm_Allocate_VLAN]![Customer] & &quot&quot;
Call DB.Execute(sSQL)
End If

RS.Close
DB.Close

Set RS = Nothing
Set DB = Nothing

End Function

 

[jebry]

Hi!

Try using this:

sSQL = &quot;SELECT * FROM tbl_Customer_Details '&quot; & _
&quot;WHERE Customer = &quot; & [Forms]![frm_Allocate_VLAN]![Customer] & &quot;'&quot;


hth


Jeff Bridgham
bridgham@purdue.edu

 

[maximo1970]

Just tried that one and now get

Run-time error `13`:

type mismatch

 

[jebry]

Hi!

Well, I need more details. In the customer details table what type of field is customer and what information is stored there? On the form Customer apparantly holds the name of the customer which must be a text value and that is why I added the single quotes. But your type mismatch error indicates that the field customer is not text.

What type of control is Customer on your form (text box, combo box)?



Jeff Bridgham
bridgham@purdue.edu

 

[maximo1970]

The field is text in the table and is a combo box on the form.

Does that point you in the right direction?

The aim is to be able to lookup existing customers and to be able to type in a new customer then update the customers table.

Cheers

Garry

Thanks for your help with this.....

 

[jebry]

Hi Garry!

Maybe you can send your Db for me to look at because I am missing something here. If you can, you will need to zip it since our Outlook security doesn't allow .mdbs through.



Jeff Bridgham
bridgham@purdue.edu