Solved Commandbars invalid procedure call

[Garren.Shannon]

Hey all,

First post here. Been a long time since I have done any database work so I am on a steep relearning curve here. I am building a database for our school transportation department to track ridership. They have been using spreadsheets. :-(

I crafted the following relationships and have a few key things I wanted to include. The depart and return times are date/time data types but I just want the time. Access does not have a time picker but I found a VB coded one from another fellow as a sample. I imported that into my project from his sample database. Using Access 2019 for both... the sample database works fine as a time picker (really cool actually) but I get "Invalid procedure call or argument" when I call the same code in the module.

Debug takes me to in the imported module mod_DateTimePicker. This same module works fine in the sample so I can't find what's causing it to not call this function correctly.

Public Function SetTimePicker(Optional Minutes As Integer = 15, _
Optional TimeRangeStart As Date = #12:00:00 AM#, _
Optional TimeRangeEnd As Date = #11:59:00 PM#, _
Optional ControlName As String)

TempVars!TimeRangeStart = TimeRangeStart
TempVars!TimeRangeEnd = TimeRangeEnd
TempVars!ControlName = ControlName

CommandBars("DateTimePicker").Controls(1).Caption = "Time Picker"
CommandBars("DateTimePicker").Controls(1).Parameter = Minutes

End Function

 

[theDBguy]

Hi. Welcome to AWF!

My guess is that the sample db has a Toolbar for picking the time, and your db does not. If so, you'll have to find the code that creates the toolbar in the sample db and run it first on your db before trying to pick a time entry.

Hope that helps...

 

[Garren.Shannon]

Thanks for the quick reply. I have the sample database, where would I look for this?

 

[theDBguy]

Thanks for the quick reply. I have the sample database, where would I look for this?

Are you able to share it here? We can help you locate the code if we can see it.

 

[Garren.Shannon]

I think it is in here... This is from mod_DateTimePicker.

 

[theDBguy]

I think it is in here... This is from mod_DateTimePicker.

Unfortunately, no, it's not in there. Look for something with something like the following in them:

CommandBars.Add
or
CommandBarControls.Add

 

[theDBguy]

I found it! It's in this one.

Public Sub CreateDateTimePickerShortcut()

    Dim cbr As Object
    Dim ctrl0 As Object
    Dim ctrl1 As Object
    Dim ctrl2 As Object
    Dim ctrl3 As Object

    On Error GoTo ProcError

    'Change True to False in the following line to add a permanent menu
    Set cbr = CommandBars.Add("DateTimePicker", 5, , -1)
    With CommandBars("DateTimePicker").Controls

        Set ctrl0 = .Add(Type:=1, Parameter:="15", Temporary:=-1)
        With ctrl0
            .Caption = "Date Time"
            .OnAction = "=fnDateTimePicker()"
            .FaceID = 768
            .Visible = -1
            .Enabled = -1
            .BeginGroup = 0
            .ToolTipText = "Date Picker"
            .Width = 166
        End With

    End With

ProcExit:
    Exit Sub 'change to function if needed

ProcError:
    MsgBox Err.Number & vbCrLf & Err.Description, , "Script error"
    Debug.Print "Script error", Err.Number, Err.Description
    Resume ProcExit

End Sub

You'll need to import that one too and execute it first.

PS. I downloaded Dale's sample from EE.

Microsoft Access Date/Time Picker | Experts Exchange

Learn more about Microsoft Access Date/Time Picker from the expert community at Experts Exchange

www.experts-exchange.com

 

[Garren.Shannon]

Thanks... Dale actually reached out to me with a reply and told me to export with "Menus and Toolbars" selected. I had not done that prior but now its working.

 

[theDBguy]

Thanks... Dale actually reached out to me with a reply and told me to export with "Menus and Toolbars" selected. I had not done that prior but now its working.

Glad to hear you got it sorted out. Good luck with your project.