Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Posts
    14

    Unanswered: Help writing VBA code

    Not sure how to write this code.

    I've got a form which shows a field called "BookingTypeID" from the table BookingType. This determines which query to be used when the booking is done. There are three BookingTypes:

    Normal
    Lesson
    Private Lesson

    Each one should initiate a different query. Query1, Query2 and Query3.

    How do I do a VBA code so that when I press a button, it'll run the correct Query, based on the BookingType for that particular record present in the form.

  2. #2
    Join Date
    Sep 2004
    Posts
    6
    I'm not really familiar with VBA but i will try to help you anyway:

    This is rough code and sure there is better/simplier solution with less code but now we need something workable for the start;
    Code:
    If BookingTypeID.text = "1" Then 'i assume you retrieve only ID with numerical value
    Dim mySQL
    mySQL = " sql of query1 goes here"
    Docmd.runSQL mySQL
    ElseIf BookingTypeID.text = "2" Then
    Dim mySQL2
    mySQL = " sql of query2 goes here"
    Docmd.runSQL mySQL2
    ElseIf BookingTypeID.text = "3" Then
    Dim mySQL3
    mySQL = " sql of query3 goes here"
    Docmd.runSQL mySQL3
    End If
    or if you fill BookingTypeID with text like Normal Lesson Private Lesson it should be:
    Code:
    If BookingTypeID.text = "Normal" Then
    Dim mySQL
    mySQL = " sql of query1 goes here"
    Docmd.runSQL mySQL
    etc....
    happy coding

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Same idea but tided up
    Code:
    'defined as string instead of variant
    Dim strSQL As String
    
        'use the select case statement instead of IF
        Select Case BookingTypeID.Text
            Case 1
                strSQL = "SQL of Query 1"
            Case 2
                strSQL = "SQL of Query 2"
            Case 3
                strSQL = "SQL of Query 3"
            Case Else
                MsgBox "Error"
        End Select
        
        'runs the select sql Statement
        DoCmd.RunSQL strSQL

  4. #4
    Join Date
    Nov 2003
    Posts
    14
    /edit

    sorted
    Last edited by Viking-ex; 02-07-05 at 08:09.

  5. #5
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    I presume that your using a combo box so you could just replace 1, 2 and 3 with the corresponding names,

    write wour SQL statements into the variable (im presuming they are action querys if not i can think again) and run from there, so no definition of variables for each different statement they should be picked up by the text value of your combo

    hope that makes sense
    Dave

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •