Calling Function from Sub

[Acropolis]

This is doign my head in now.

I have 28 combo boxes on a form, which I want to insert data into the table as they are chaged. Each one will pass the same sets of data just with different parameters which come from the form.

Rather than putting the same code to insert on each of the 28 combo boxes I thought it would be easier to create a function to do it and pass the parameters to it through a sub on the AfterUPdate event of the combo box.

I need to pass 4 parameters, if I only put 1 in there it works fine, but when I start putting more in it doesnt work and I get compile errors or syntax errors.

Could someone point me in the right direction.

Sub routine:

Private Sub cboMonday1_AfterUpdate()

If Me.cboMonday1 = 1 Then
    Me.cboMonday1.BackColor = vbGreen
    Me.cboMonday1.ForeColor = vbBlack
Else
    If Me.cboMonday1 = 2 Then
        Me.cboMonday1.BackColor = vbRed
        Me.cboMonday1.ForeColor = vbWhite
    Else
        If Me.cboMonday1 = 3 Then
            Me.cboMonday1.BackColor = vbBlue
            Me.cboMonday1.ForeColor = vbWhite
        Else
            Me.cboMonday1.BackColor = vbWhite
            Me.cboMonday1.ForeColor = vbBlack
        End If
    End If
End If
If IsNull(Me.cboMonday1) Then
    
Else
    Dim MemberID, AvailabilityID, TimeslotID As Integer
    Dim AvailableDate As Date
    
    MemberID = 37
    AvailabilityID = Me.cboMonday1
    MemberID = 37
    TimeslotID = 1
    AvailableDate = Me.txtMonday
    
    insertavilability (MemberID, AvailabilityID, TimeslotID, AvailableDate)
End If
End Sub

Function is Module 1:

Public Function insertavilability(MemberID As Integer, AvailabilityID As Integer, TimeslotID As Integer, AvailableDate As Date)
DoCmd.RunSQL "INSERT INTO tblmemberavailability (MemberID, AvailableDate, AvailabilityID, TimeslotID) VALUES (" & MemberID & ",'" & AvailableDate & "'," & AvailabilityID & "," & TimeslotID & ")"
End Function

Thanks

 

[pbaldy]

Try surrounding AvailableDate with # instead of '.

 

[Acropolis]

I'll give that a try, but I can't even get it working with only MemberID and AvailabilityID been passed, which should both be Integer.

 

[spikepl]

Less is more. A common rookie mistake is to concoct a lot of code/SQL and then cry when it doesn't work. Instead, procede in smal steps. Make something work (fx one param) then add the next one and make that work etc etc. At each small step, if that fails, you'll namely know which area you touched (and "screwed up")

 

[Acropolis]

Precisley what I have tried to do and getting no where.

If I call the function with only 1 parameter defind in it then it works fine, but the second i put a second one in there I get errors, I have tried everything I can think of and followed the prompts that come up when doing it and nothings seesm to be working.

Works fine

insertavilability (MemberID)

Public Function insertavilability(MemberID As Integer)

Doesn't work - get error "Compile Error: Expected: =

insertavilability (MemberID, AvailabilityID)

Public Function insertavilability(MemberID, AvailabilityID As Integer)

Have tried every permitation I can and nothing seems to work.

 

[pbaldy]

Try calling it without the parentheses, or use Call.

 

[Acropolis]

insertavilability MemberID, AvailabilityID

I get - "Compile error: ByRef argument type mismatch"

By putting in call I get the same error come up

Call insertavilability(MemberID, AvailabilityID)

 

[pbaldy]

Try explicitly declaring all variables, and make sure they're the same in both places. Note that this:

Dim MemberID, AvailabilityID, TimeslotID As Integer

Declares 2 Variants and 1 Integer. You want

Dim MemberID As Integer, AvailabilityID As Integer, TimeslotID As Integer

 

[Acropolis]

Thanks for the help thats got it now and its working fine. Had some strange issue with a forgein constraint, but got that sorted now.

Here is how it ended up

    Dim MemberID As Integer
    Dim TypeID As Integer
    Dim TimeslotID As Integer
    Dim AvailableDate As Date
    
    MemberID = 37
    TypeID = Me.cboMonday1
    TimeslotID = 1
    AvailableDate = Me.txtMonday
       
    Call insertavilability(MemberID, TypeID, TimeslotID, AvailableDate)

And

Public Function insertavilability(MemberID As Integer, TypeID As Integer, TimeslotID As Integer, AvailableDate As Date)
DoCmd.RunSQL "INSERT INTO tblmemberavailability (MemberID, AvailableDate, AvailabilityID, TimeslotID) VALUES (" & MemberID & ",' " & AvailableDate & " '," & TypeID & "," & TimeslotID & ")"
End Function

I am new to this and learning lots with it all the time, largely thanks to the help I receive here.

Thanks

 

[pbaldy]

Happy to help!