Error 2950

S

StudentHelp

New member
Local time
Today, 21:27
Joined
Apr 1, 2014
Messages
7
Hey im new to the forums and desperately need some help!

I am trying to run a macro which checks if the data which is being entered is already in the table, with error messages before running an append query at the end, however whenever i try to run the query i get a '2940 error' with all the boxes blanked out. the Microsoft website says that it is because the database isn’t trusted, but i have trusted it a number of times on different machines, i am able to run other macros fine however this one is giving me grief!

any help would be appreciated!
i have attached a screen short of the error message aswell :)
 

Attachments

  • 2950.jpg
    2950.jpg
    20.3 KB · Views: 276
Hello StudentHelp, Welcome to AWF :)

Convert the Macro to a VBA, debugging with Macro is hard, however its easier with VBA. So if you get to it, I am sure plenty of users would be able to offer you help.
 
Thanks for the reply, i have no idea about VBA but heres the converted code, if its any help
 

Attachments

  • VBA 2950.jpg
    VBA 2950.jpg
    89.4 KB · Views: 256
Remove the = sign before the word BookingAvailable, its not needed which is also the reason for the error.
 
Hi again

Thanks its working to a point now, it dosent want to run the append query at the end or give me the confirming message box, any more ideas?
 
I only helped you to remove the error. If you want the code to actually work, you might need to give me a bit of information. What is the code supposed to do? What is it not doing? Please could you also paste the code here? Make sure you use Code Tags when posting VBA Code
 
the database is based on a estate agent, so i have created a booking screen where i want the macro to run to check if the data entered into the form is already in the table e.g. the property already has a viewing on the date and time entered. if its all ok then i want the marco to run an append query to add the data inputted in the form to the 'Viewings' table. at the moment it picks up on any errors, but dosent run the append query at the end if it is all clear, it just sits there and seems to do nothing:

here is the VBA code:

Option Compare Database
'------------------------------------------------------------
' BookingCheckerV2
'
'------------------------------------------------------------
Function BookingCheckerV2()
On Error GoTo BookingCheckerV2_Err
Dim BookingAvailable As Variant
BookingAvailable = "Yes"
If (Eval("DLookUp(""[Viewing ID]"",""Viewing"",""Staff_Viewing_ID= Forms![Booking Screen]![AgentCombo] AND [Viewing Period]= Forms![Booking Screen]![ViewingCombo] AND [Date_of_viewing]= Forms![Booking Screen]![ViewingDate]"") Is Not Null")) Then
Beep
MsgBox "Agent Not Available", vbOKOnly, "Error"
Else
BookingAvailable = "No"
End If
If (Eval("DLookUp(""[Viewing ID]"",""Viewing"",""Property_Viewing_ID= Forms![Booking Screen]![PropertyCombo] AND [Viewing Period]= Forms![Booking Screen]![ViewingCombo] AND [Date_of_Viewing]= Forms![Booking Screen]![ViewingDate]"") Is Not Null")) Then
Beep
MsgBox "Property Already Has A Booking", vbOKOnly, "Error"
Else
BookingAvailable = "No"
End If
If (Eval("DLookUp(""[Viewing ID]"",""Viewing"",""Customer_Viewing_ID= Forms![Booking Screen]![BuyerCombo] AND [Viewing Period]= Forms![Booking Screen]![ViewingCombo] AND [Date_Of_Viewing]= Forms![Booking Screen]![Viewing Date]"") Is Not Null")) Then
Beep
MsgBox "Buyer Is Already On A Viewing", vbOKOnly, "Error"
Else
BookingAvailable = "No"
End If
If (BookingAvailable = "Yes") Then
Beep
MsgBox "Booking Confirmed", vbOKOnly, "Sucess"
DoCmd.OpenQuery "AppendQuery", acViewNormal, acEdit
End If

BookingCheckerV2_Exit:
Exit Function
BookingCheckerV2_Err:
MsgBox Error$
Resume BookingCheckerV2_Exit
End Function
 
ok lets try again :p

Code: 
Option Compare Database
'------------------------------------------------------------
' BookingCheckerV2
'
'------------------------------------------------------------
Function BookingCheckerV2()
On Error GoTo BookingCheckerV2_Err
    Dim BookingAvailable As Variant
    BookingAvailable = "Yes"
    If (Eval("DLookUp(""[Viewing ID]"",""Viewing"",""Staff_Viewing_ID= Forms![Booking Screen]![AgentCombo] AND [Viewing Period]= Forms![Booking Screen]![ViewingCombo] AND [Date_of_viewing]= Forms![Booking Screen]![ViewingDate]"") Is Not Null")) Then
        Beep
        MsgBox "Agent Not Available", vbOKOnly, "Error"
    Else
        BookingAvailable = "No"
    End If
    If (Eval("DLookUp(""[Viewing ID]"",""Viewing"",""Property_Viewing_ID= Forms![Booking Screen]![PropertyCombo] AND [Viewing Period]= Forms![Booking Screen]![ViewingCombo] AND [Date_of_Viewing]= Forms![Booking Screen]![ViewingDate]"") Is Not Null")) Then
        Beep
        MsgBox "Property Already Has A Booking", vbOKOnly, "Error"
    Else
        BookingAvailable = "No"
    End If
    If (Eval("DLookUp(""[Viewing ID]"",""Viewing"",""Customer_Viewing_ID= Forms![Booking Screen]![BuyerCombo] AND [Viewing Period]=  Forms![Booking Screen]![ViewingCombo] AND [Date_Of_Viewing]= Forms![Booking Screen]![Viewing Date]"") Is Not Null")) Then
        Beep
        MsgBox "Buyer Is Already On A Viewing", vbOKOnly, "Error"
    Else
        BookingAvailable = "No"
    End If
    If (BookingAvailable = "Yes") Then
        Beep
        MsgBox "Booking Confirmed", vbOKOnly, "Sucess"
        DoCmd.OpenQuery "AppendQuery", acViewNormal, acEdit
    End If
 
BookingCheckerV2_Exit:
    Exit Function
BookingCheckerV2_Err:
    MsgBox Error$
    Resume BookingCheckerV2_Exit
End Function
 
Code: 
Option Compare Database
[COLOR=Green]'------------------------------------------------------------
' BookingCheckerV2
'------------------------------------------------------------[/COLOR]
Function BookingCheckerV2() As Boolean
On Error GoTo BookingCheckerV2_Err
    Dim staffID As Long, viewPeriod As String, viewDate As String
    Dim propertyID As Long, buyerID As Long
    
    staffID = Forms![Booking Screen]![AgentCombo]
    viewPeriod = Forms![Booking Screen]![ViewingCombo]
    viewDate = Format(Forms![Booking Screen]![ViewingDate], "\#mm\/dd\/yyyy\#")
    propertyID = Forms![Booking Screen]![PropertyCombo]
    buyerID = Forms![Booking Screen]![BuyerCombo]
    
    If DCount("*", "Viewing", "Staff_Viewing_ID = " & staffID & " AND [Viewing Period] = '" & viewPeriod & "' AND [Date_of_viewing] = " & viewDate) <> 0 Then
        MsgBox "Agent Not Available", vbOKOnly, "Error"
        BookingCheckerV2 = False
        Exit Function
    End If
    
    If DCount("*", "Viewing", "Property_Viewing_ID = " & propertyID & " AND [Viewing Period] = '" & viewPeriod & "' AND [Date_of_viewing] = " & viewDate) <> 0 Then
        MsgBox "Property Already Has A Booking", vbOKOnly, "Error"
        BookingCheckerV2 = False
        Exit Function
    End If
     
    If DCount("*", "Viewing", "Customer_Viewing_ID = " & buyerID & " AND [Viewing Period] = '" & viewPeriod & "' AND [Date_of_viewing] = " & viewDate) <> 0 Then
        MsgBox "Buyer Is Already On A Viewing", vbOKOnly, "Error"
        BookingCheckerV2 = False
        Exit Function
    End If
    
    BookingCheckerV2 = True
BookingCheckerV2_Exit:
    Exit Function
BookingCheckerV2_Err:
    MsgBox Error$
    Resume BookingCheckerV2_Exit
End Function
Then on the Click of the button that calls this fucntion, use.
Code: 
Private Sub buttonName_Click()
    If BookingCheckerV2 Then
        MsgBox "Booking Confirmed", vbOKOnly, "Sucess"
        DoCmd.OpenQuery "AppendQuery"
    End If
End Sub
 
You must log in or register to reply here.

Similar threads

A
Solved Database gets error message after transferring from Access 2010 to Runtime 2013
Replies
7
Views
544
Gasman
Gasman
C
Access 21 problem with where command
2
Replies
24
Views
1,103
Isaac
Isaac
C
Solved The Expression ... you entered as the event property setting produced the following error: Requested type library or wizard is not a vba project
Replies
0
Views
249
CarltonJ
C
R
Solved Dreaded 'Write Conflict' error after adding a new field to Access form
Replies
12
Views
800
ricardocpa
R
A
Solved VBA compact-and-repair of a closed (corrupted) database from a new, blank Access file
2
Replies
24
Views
1,575
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom