vba toying with me

[Pete Morris]

as you can probably tell, I'm new to this. The code has run through me several times, with me getting closer to the solution??
Can anybody tell me what is wrong with this as I am getting syntax error at the dlookup customer section.


Private Sub customer_notinlist(newdata As String, _
response As Integer)
Dim strcustomer As String
Dim intReturn As Integer
strcustomer = newdata
intReturn = MsgBox("Customer is not on the list." & _
"Do you want to add this Customer?", _
vbQuestion + vbYesNo, "Changes")
If intReturn = vbYes Then
DoCmd.OpenForm FormName:="Customer Lookup", _
DataMode:=acAdd, _
Windowmode:=acDialog, _
OpenArgs:=strcustomer
If IsNull(DLookup("Customer", _
"tblCustomer lookup", _
"[Customer]=""")) Then
response = acDataErrContinue
Else
response = acDataErrAdded
End If
Exit Sub

End If
End Sub

greatful for any help.

Thanks, Pete.

 

[Mile-O]

This is the line you have:

If IsNull(DLookup("Customer", "tblCustomer lookup", "[Customer]=""")) Then

• Your table contains a space. Enclose it within square brackets. "[tblCustomer lookup]" It's generally not a good practice to assign names to objects with spaces as, as you've found out, it causes more problems than it should if you don't know what you are doing.
• "[Customer] = """ - What you are doing here is setting the criteria that you are wanting to lookup the Customer field where Customer is equal to a quotation mark. I'm sure that's not what you want to do but I'm guessing you are wanting to lookup up the Customer field where said field is a null value; therefore you can use the SQL style criteria Is Null

You should have something like this:

If IsNull(DLookup("[Customer]", "[tblCustomer lookup]", "[Customer] Is Null)) Then

That, however, doesn't make much sense. Surely you are wanting to check if the value held in the variable strCustomer is in the table tblCustomer lookup.

If IsNull(DLookup("[Customer]", "[tblCustomer lookup]", "[Customer] = """ & strCustomer & """")) Then

 

[Pete Morris]

OK that works nicely, however now that I have checked that whether the customer exists in the database I want to continue filling out the original form.
Is code needed to continue on with the original form, or is it a command of some sort?

 

[Pete Morris]

ok so still banging my head against the computer.
Have figured out that I need to put some code into the load event of the new form for it to add the new data.

Am cutting text from a book and trying to bend it to my needs, though I don't really understand it (not a good combination I know).

Here's the code

Private Sub Form_Load()
Dim strCustomer As String
If IsNull(Me.OpenArgs) Then Exit Sub
End Sub

this allows me to add the missing text but then when I go back to the main form I can't continue filling it out??

a very confused and not very bright Pete Morris