Dcount Type Mismatch (1 Viewer)

[Sharkiness]

Thanks for that PBALDY,

Now that I have this working.

I want to make sure that if the surname is not complete that a message box will appear advising.

I have got it working in as much that it will advise the box is not empty but it will go ahead and add the record anyway.

 

[pbaldy]

You're adding with SQL or a bound form? For a bound form I'd use the before update event:

http://www.baldyweb.com/BeforeUpdate.htm

For SQL, it would help to see the structure of your code. Simple If/Then logic should handle it.

 

[Sharkiness]

Here it is:

[COLOR=black][FONT=Verdana]Dim strSQLAdd As String[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]Dim strSQLchq As String[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]strSQLAdd = "INSERT INTO tblCustomers(CustomerID, Title, Forename, MiddleName, Surname, DOB, NINO, Status_of_Compromise, UTR)" & _[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]"VALUES ('" & Me.txtCustomerID & "','" & Me.txtTitle & "','" & Me.txtForename & "','" & Me.txtMiddleName & "','" & Me.txtSurname & "','" & Me.txtDOB & "','" & Me.txtNINO & "','" & Me.txtStatusOfCompromise & "','" & Me.txtUTR & "');"[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]strSQLChk = DCount("*", "tblCustomers", "Forename=" & Chr(34) & txtForename & Chr(34) & " And Surname = " & Chr(34) & txtSurname & Chr(34))[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]If Len(Me.txtForename & vbNullString) = 0 Or Len(txtSurname & vbNullString) = 0 Then[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]Cancel = True[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]MsgBox "You have not entered a Forename or Surname"[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
 
[FONT=Verdana][COLOR=black]If Me.txtCustomerID.Value = "" Then[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]Me.txtCustomerID.Value = "T" & Format(Date, "yyyymmdd") & Format(Time(), "hhnnss")[/FONT][/COLOR]
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
 
[COLOR=black][FONT=Verdana]If strSQLChk > 0 Then[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]MsgBox "Record already exists with this data", vbOKOnly[/COLOR][/FONT]
[COLOR=black][FONT=Verdana]Else[/FONT][/COLOR]
 
[FONT=Verdana][COLOR=black]DoCmd.RunSQL strSQLAdd[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]MsgBox "The new individual has been added"[/COLOR][/FONT]
 
 
[FONT=Verdana][COLOR=black]End If[/COLOR][/FONT]
[FONT=Verdana][COLOR=black]End Sub[/COLOR][/FONT]

 

[pbaldy]

It sounds like after your message box advising them you want

Exit Sub

so no further code is run.

 

[Sharkiness]

It sounds like after your message box advising them you want

Exit Sub

so no further code is run.

I tried that, but i get a compile error stating Block if without End If.

If I put the END Sub after the END IF i get a compile error saying only comments maty appear after End Sub, End Function, or End Property.

Very confused.

Thanks for your quick replies up to now. Would you like me to strip my database and provide a sample

 

[Kiwiman]

Howzit

You want it here

If Len(Me.txtForename & vbNullString) = 0 Or Len(txtSurname & vbNullString) = 0 Then
Cancel = True
MsgBox "You have not entered a Forename or Surname"
exit sub
End If

 

[Sharkiness]

Thanks Kiwiman,

That works but has thrown up a new issue where I want the Customer ID to be populated if the value is null.

I have tried placing before the rest of the code and again just before the
DoCmd.RunSQL strSQL Add

It doesnt seem to work at all which then throws an error when adding to the table as the ID is required.

Also another point, is there any way to stop the 'You are about to append 1 record'. I think this may be by setwarnings to false but just not bothered trying yet.

Cheers

 

[Kiwiman]

Howzit

Try

If Me.txtCustomerID.Value = "" or isnull(Me.txtCustomerID) Then
Me.txtCustomerID.Value = "T" & Format(Date, "yyyymmdd") & Format(Time(), "hhnnss")
End If

To stop the warning message you can either turn the warnings off then on or

currentdb.execute strSQLAdd