Data entry using multiple combo boxes

[A10Instructor]

Hi,
I have 5 tables in my data base. My ultimate goal is to print a certificate. I would like to create a form in which you use combo boxes to select the desired information from each table that would go onto the certificate. I then wish to dsplay the selected info on the same form for verification prior to printing.
I have a combo box with the following code on a form/subform that works very well. I use this to edit the tables and would like to use this for this effort as well:

[blue]
Sub SetFilter()

Dim LSQL As String

LSQL = "select * from course_info"
LSQL = LSQL & " where crscode = '" & cboSelected & "'"

Form_Editcoursesub.RecordSource = LSQL

End Sub

Private Sub cboSelected_AfterUpdate()

'Call subroutine to set filter based on selected course
SetFilter

End Sub

Private Sub Form_Open(Cancel As Integer)

'Call subroutine to set filter based on selected course
SetFilter

End Sub
[/blue]

Any clues as to how to tackle this?





A10 Instructor
"The World is My Classroom

 

[willir]

Sub SetFilter()

    Dim LSQL  As String
    
    LSQL = "select * from course_info"
    LSQL = LSQL & " where crscode = '" & [COLOR=blue]Me.[/color]cboSelected & "'"
    
    Form_Editcoursesub.RecordSource = LSQL[COLOR=blue]
    Form_Editcoursesub.Requery
    [/color]
End Sub

You need to a) reference the control on the form "Me!" or "Me.", and you need to Requery the record source to display the new results. You can add a command button to print the form if the depicted results in correct.

Note that you can hide / unhide "controls" on a form...

Assuming...
cmdPrint is the command button to print the form...

Sub SetFilter()

Dim LSQL  As String
    
LSQL = "select * from course_info"
If Len(Me.cboSelected & "") Then
    LSQL = LSQL & " where crscode = '" & Me.cboSelected & "'"
    
    Form_Editcoursesub.RecordSource = LSQL
    Form_Editcoursesub.Requery
    
    Me.cmdPrint.Visible = True
Else
    Me.cmdPrint.Visible = False
End If

End Sub

Note in the above, I test to see if there is a value in the cboSelected combo box. If present, requery the form based on the SQL statement. (Note, you can also use Filter = ... and FilterOn = True). And, make the command button cmdPrint to be visible. Otherwise, if there is nothing in the cboSelected, hide the print button.

Richard

 

[PHV]

Willir, FYI, resetting the RowSource property automatically trigger the Requery method.

 

[willir]

Thanks PHV. You are correct (but nothing new there).

I am just accustomed to the RowSource situation where I have to requery. Also, when using separate forms (Form A is the customer order with subform for order detail, Form B with the pick list on the double-click event), the requery from Form B will force populate / update the subform in Form A with new values created by the Form A double click event.

I was just working on automatic pilot.

 

[A10Instructor]

All,

Thanks for the info/tips. I still can't seem to get them to work. Maybe if I restate what I'm trying to do it may clear things up.

I have a table (course_info) that has five columns (crscode, crsname, pdscode, crshours, ccafhours). I wish to creat a form that has a combobox based on crsname and autofill the remaining columns in text boxes.

I then want to repeat this process with 7 other tables that contain different data. My ultimate goal is to save the user selected data to a table called class_data and print this data out on a diploma form.

Any help will greatly be appreciated!

Thanks

A10 Instructor
"The World is My Classroom