Form to search table 1

[Omono]

I have a table with 7 fields. I envision that from a main form a user can select the "search" function. I have created a form with an option group permitting the user to select on which of three possible fields to conduct the search. Upon making a selection from the option group, lets say to search by employee number, another form pops up asking the user to input the employee number to find (this 2nd form includes an unbound textbox.) This is where I'm stumbling. Of course, in this example I want the table searched by the employee number field. After the record is found, the appropriate form (a different form than the two I've mentioned above) should launch and display the found record. None of this involves using the primary key. Being a novice, suggestions and advice are readily welcomed.

Thanks in advance
O'Neal
ofreemanjr@yahoo.com

 

[tunsarod]

Your proposal is certainly feasible but before you spend any more time pondering how to code three forms try this.

Create a main form to display a single record at a time. Use the wizard to create a Columnar form based on thyour table. You can adapt it to suite your needs later.

Now make sure the toolbox wizard is turned on and click on the drop-combo tool. Place a ComboBox on the form and follow the wizards instructions. When asked select the option to - Find a record on my form based on the field I entered into my Combo Box.

When choosing a field make sure its one of the three search fields you have in mind.

Once the field is completed try it out and you will find that it will enable you to quickly get to the required record.

Next add an Option Group to the form and set three options to represent the three search fields you have in mind.

While the Form is in Design View highlight the Row Rource property for the ComboBox and copy the entire text to the clipboard.

Now set the After_Update Event for the option group to Event Procedure and click the build button. The code module window will open and you should see the ComboBox After_Update Event already there along with the After_Update Event procedure for the Option Group.

Obviously the OptionGroups After_update event is empty.

Place the cursor inside the Option Group After_Update Event and type the name of the Combo Box and an equals sign. Now press Ctrl V to past the code from the ComboBox's Row Source property. You will need to dd quotation markles at either end of the pasted text.

Now copy the entire line of code you just added and duplicate it twice.

Add some code to utilise the Select case statement and add a requery command and the drop box's operation will change according to the sselected search field.

See my example below which is based a tale called Prospects where the Primary Key field is called PrimaryID:

------------------------------------------
Option Compare Database
Option Explicit

Sub SearchCombo_AfterUpdate()
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[PrimaryID] = " & Me![SearchCombo]
Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub

Private Sub OptionFrame_AfterUpdate()
Select Case OptionFrame
Case 1
SearchCombo.RowSource = "SELECT DISTINCTROW [Prospects].[ID], [Prospects].[Field1] FROM [Prospects];"
Case 2
SearchCombo.RowSource = "SELECT DISTINCTROW [Prospects].[ID], [Prospects].[Field2] FROM [Prospects];"
Case 3
SearchCombo.RowSource = "SELECT DISTINCTROW [Prospects].[ID], [Prospects].[Field3] FROM [Prospects];"
Case Else
End Select

SearchCombo.Requery

End Sub

--------------------------------------------

Regards
Rod

 

[Omono]

Thanks Rod for the quick response. I've tried it and it will work great! Tahnks again....
O'Neal