Link to home
Start Free TrialLog in
Avatar of gigifarrow
gigifarrow

asked on

Using a form to search like a Parameter query

I have a form that based on your selection in the option list. Which is three selections MWO, ModKit.Vehicle Serial Number,

The unbound drop down are MWO, ModKit,Vehicle .Serial Number
They are  suppose to search and put the data on the subform. My drop downs do not have data so I cant even tell if it is finding the data to populate my sup form .

 I am so confused. On how to do this. This is the first time I have tried to do a query in a form, I always use parameter queries.,  Because it is easier, but my boss wants to use a form. Below is the DB


 Option Compare Database
Option Explicit

Dim strTitle As String
Dim strPrompt As String
Dim strFilter As String
Dim strQuery As String
Dim strRecordSource As String
Dim strSQL As String
Select Case Option

Case OpMWo
Me.MODKIT.Enabled =False
Me.SerialNumber.Enabled=False

Case opMODKit
Me.MWO.Enabled =False
Me.SerialNumber.Enabled=False

Case opVehicleSerialNumber
Me.MWO.Enabled =False
Me.MODKIt.Enabled =False




Private Sub MODKit_AfterUpdate()

   Dim strMODKit As String
   If strMODKit = "" Then
      GoTo ErrorHandlerExit
   End If
  strFilter = "[Vehicle Serial Number] = " & Chr$(39) & strVehicleSerialNumber & Chr$(39)
   Me![FilterString] = strFilter
   DoCmd.RunCommand acCmdSaveRecord
   strRecordSource = "qryMODHistoryMike"
   strQuery = "qryMODKit"
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & strFilter & ";"
   Debug.Print "SQL Statement: " & strSQL
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _
      & " records found"
   Me![SubFrmMWO].Form.RecordSource = strQuery
     
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub



Private Sub MWO_AfterUpdate()

   Dim strMWO As String
   If strVehicleSerialNumber = "" Then
      GoTo ErrorHandlerExit
   End If
  strFilter = "[MWO] = " & Chr$(39) & strMWO & Chr$(39)
   Me![FilterString] = strFilter
   DoCmd.RunCommand acCmdSaveRecord
   strRecordSource = "qryMODHistoryMike"
   strQuery = "qryMWO"
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & strFilter & ";"
   Debug.Print "SQL Statement: " & strSQL
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _
      & " records found"
   Me![SubFrmMWO].Form.RecordSource = strQuery
     
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub




Private Sub VehicleSerialNumber_AfterUpdate()

   Dim strVehicleSerialNumber As String
   If strVehicleSerialNumber = "" Then
      GoTo ErrorHandlerExit
   End If
  strFilter = "[Vehicle Serial Number] = " & Chr$(39) & strVehicleSerialNumber & Chr$(39)
   Me![FilterString] = strFilter
   DoCmd.RunCommand acCmdSaveRecord
   strRecordSource = "qryMODHistoryMike"
   strQuery = "qrySerialNumber"
   strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
      & strFilter & ";"
   Debug.Print "SQL Statement: " & strSQL
   Debug.Print CreateAndTestQuery(strQuery, strSQL) _
      & " records found"
   Me![SubFrmMWO].Form.RecordSource = strQuery
     
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub


Private Sub Form_Load()
On Error GoTo ErrorHandler
 
   DoCmd.RunCommand acCmdSizeToFitForm
   Me![Filter].Value = 1
   Me![FilterString] = ""
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & _
      Err.Description
   Resume ErrorHandlerExit

End Sub
HelpWithFormSearch.accdb
Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image

Your attachment is reported as 0 bytes and, indeed, it cannot be downloaded. Can you to upload it again?
Avatar of gigifarrow
gigifarrow

ASKER

Sorry had it open when downloaded it.
Helpwithqueryinaform.accdb
I've had trouble downloading that file with Firefox. Google Chrome did it OK, though.

The database doesn't seem to be related to the code or to the question. Are you sure that it's the right one?
OMG! I am so sorry. No that is not the right DB. Please bear with me. I have been under alot of stress from my job to get this figured out. Here is the correct one.
HelpWithFormSearch.accdb
Sorry, I keep getting called away.

I don't know how the choices from the three combos and the three options buttons are supposed to work together, but you can fill their lists by setting the Row source of each to its matching query.
ASKER CERTIFIED SOLUTION
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
...I think this is more of what you wanted...
Database52.mdb
Jeff you are so good!! You have helped me out alot on this site. And just want you to know that I really appreciate it. Going to try it out tonight when I get home from work. Let you know what happens!!!