Solved Help this is not working - Dlookup (1 Viewer)

Number11

Member
Local time
Today, 12:55
Joined
Jan 29, 2020
Messages
607
I am trying to get this to work, so the form has this and i want it to promp message if the customer is already on the database, but this isnt working it prompts even if they are not :(

Private Sub AccountNo_BeforeUpdate(Cancel As Integer)
Dim dr As VbMsgBoxResult

If (Not IsNull(DLookup("[Account No]", "Master", "[AccountNo] ='" & Me!AccountNo & "'"))) Then

dr = Dialog.Box("A request is already on the database - are you sure you wish to add another? ", vbYesNo + vbQuestion, "Confirm Action")

If (dr = vbYes) Then

End If

If (dr = vbNo) Then

Me.Undo

DoCmd.close acForm, Me.Name
 
Last edited:

bob fitz

AWF VIP
Local time
Today, 12:55
Joined
May 23, 2011
Messages
4,717
Assuming AccountNo is text, Try:
Code:
If DCount("[Account No]", "Master", "[AccountNo] ='" & Me!AccountNo & "'")>0 Then

 If Msgbox("A request is already on the database - are you sure you wish to add another? ", vbYesNo + vbQuestion, "Confirm Action") = vbNO Then
    Me.Undo
 End If

 DoCmd.close acForm, Me.Name
 End IF
 

Number11

Member
Local time
Today, 12:55
Joined
Jan 29, 2020
Messages
607
Assuming AccountNo is text, Try:
Code:
If DCount("[Account No]", "Master", "[AccountNo] ='" & Me!AccountNo & "'")>0 Then

If Msgbox("A request is already on the database - are you sure you wish to add another? ", vbYesNo + vbQuestion, "Confirm Action") = vbNO Then
    Me.Undo
End If

DoCmd.close acForm, Me.Name
End IF
No its a number field?
 

bob fitz

AWF VIP
Local time
Today, 12:55
Joined
May 23, 2011
Messages
4,717
So try:
Code:
If DCount("[Account No]", "Master", "[AccountNo] =" & Me!AccountNo)>0 Then

If Msgbox("A request is already on the database - are you sure you wish to add another? ", vbYesNo + vbQuestion, "Confirm Action") = vbNO Then
    Me.Undo
End If

DoCmd.close acForm, Me.Name
End IF
 

Number11

Member
Local time
Today, 12:55
Joined
Jan 29, 2020
Messages
607
So try:
Code:
If DCount("[Account No]", "Master", "[AccountNo] =" & Me!AccountNo)>0 Then

If Msgbox("A request is already on the database - are you sure you wish to add another? ", vbYesNo + vbQuestion, "Confirm Action") = vbNO Then
    Me.Undo
End If

DoCmd.close acForm, Me.Name
End IF

Thanks but this is not working at all still, even if i try and eneter with say account number 999 it promps me but nothing in the table with that account number?
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:55
Joined
Jan 23, 2006
Messages
15,361
Show us the design of table MASTER.
Is the field name AccountNo or Account No?
 

Number11

Member
Local time
Today, 12:55
Joined
Jan 29, 2020
Messages
607
in the table it is Account No and on the form the field name is AccountNo
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:55
Joined
Jan 23, 2006
Messages
15,361
Then try
If DCount("[Account No]", "Master", "[Account No] =" & Me!AccountNo)>0 Then
 

bob fitz

AWF VIP
Local time
Today, 12:55
Joined
May 23, 2011
Messages
4,717
Perhaps you need a space in Me![AccountNo] too
If DCount("[Account No]", "Master", "[Account No] =" & Me![Account No])>0 Then
 

bob fitz

AWF VIP
Local time
Today, 12:55
Joined
May 23, 2011
Messages
4,717
Number11

I see that you have now marked this thread as solved.

What was the solution.
 

Number11

Member
Local time
Today, 12:55
Joined
Jan 29, 2020
Messages
607
Number11

I see that you have now marked this thread as solved.

What was the solution.

this was correct and worked

If DCount("[Account No]", "Master", "[Account No] =" & Me!AccountNo)>0 Then

many thanks
 

Users who are viewing this thread

Top Bottom