DCount problems

[dhannant]

So I'm having some issues with this DCount in vba. I currently have vba code ask for a last name, open a form and search for that last name. Everything worked fine until... A married couple started working here. Now the code brings up both of them everytime. So what I need is for the code to look at the table called "Combined Roster" in the field "Last Name" and IF there are 2 or more people with the same last name, ask for a first name... then search for that also. Below is the code I'm using. Oh and the reason I'm not using a query is that I wanted to have the same form (called New Employee) be used when they go to edit someone so it's familiar. I didn't want to forms or an excel type layout. This just seemed easiest to me when I first set all of this up.

'------------------------------------------------------------
' EditRecord_Click
'
'------------------------------------------------------------
Private Sub EditRecord_Click()
On Error GoTo EditRecord_Click_Err
Dim lnamecount As Long
Dim lname As String
Dim fname As String
lname = InputBox("Enter Last Name", "Name Required")
'On Error Resume Next
'Check if user pressed cancel button
If StrPtr(lname) = 0 Then
    MsgBox "Edit Record Cancelled", vbInformation
    GoTo EditRecord_Click_Exit
Else
    'Check to see if there is something to look for
    If Len(lname) = 0 Then
        MsgBox "Please enter a name to edit", vbCritical
        GoTo EditRecord_Click_Exit
    Else
        'Last name present, now check to make sure no one shares the same last names
        lnamecount = DCount("*", "Combined Roster", [Last Name] = lname)
                    'DCount("[ShippedDate]", "Orders", "[ShipCountry] = '" & strCountry & "'AND [ShippedDate] > #" & dteShipDate & "#")
        If lnamecount > 1 Then
            'Two or more people share this last name, ask for first name to narrow results
            fname = InputBox("Enter First Name", "Name Required")
        Else
            'No one shares this last name, search the records
            DoCmd.OpenForm "New Employee", acNormal, "", "", acEdit, acNormal
 
            DoCmd.GoToRecord acForm, "New Employee", acLast
            DoCmd.FindRecord lname, acEntire, False, , False, acCurrent, True
            GoTo EditRecord_Click_Exit
                If StrPtr(fname) = 0 Then
                    MsgBox "Edit Record Cancelled", vbInformation
                    GoTo EditRecord_Click_Exit
                Else
                    If Len(fname) = 0 Then
                        MsgBox "Please enter a name to edit", vbCritical
                        GoTo EditRecord_Click_Exit
                    Else
                        DoCmd.OpenForm "New Employee", acNormal, "", "[Combined Roster]![Work Center]="", acEdit, acNormal
               DoCmd.GoToRecord acForm, "New Employee", acLast
                        DoCmd.FindRecord lname, acEntire, False, , False, acCurrent, True
                    End If
                End If
        End If
    End If
End If

 

[Minty]

DCount("*", "Combined Roster", [Last Name] = lname)

You need to escape the LName with ' eg = '" & Lname & "'"

 

[dhannant]

oh my bad... I originally had it like that:

lnamecount = DCount("*", "Combined Roster", "[Last Name] = '" & lname)

I started playing around with it trying to figure out what was wrong and when I failed at that I started searching forums...

Never went back and changed it to what "should've" made it work... cause I've found examples of how to do it... it just hasn't worked for me.

Also the error it gives is "Syntax error in string in query expression '[Last Name] = 'Hannant'.

 

[CJ_London]

should be

lnamecount = DCount("*", "Combined Roster", "[Last Name] = '" & lname & "'")

 

[dhannant]

jeez... I read and read and read and even after you posted it again I still missed 1 '... that was the problem. Thanks a lot guys. Feel kinda retarded now though.