Sub form help needed

[wildthingcg]

I am new to Access and need some help with adding a search box onto a Sub form.

I added a search box on my Main form that has a combo box and a text box which is accessed with a command button, it works great.
Here is the code:

Private Sub cmdSearch_Click()


If Len(cboSearchField) = 0 Or IsNull(cboSearchField) = True Then
MsgBox "You must select a field to search."

ElseIf Len(txtSearchString) = 0 Or IsNull(txtSearchString) = True Then
MsgBox "You must enter a search string."

Else

'Generate search criteria
GCriteria = cboSearchField.Value & " LIKE '*" & txtSearchString & "*'"

'Filter Employers based on search criteria
Form_Employers.RecordSource = "select * from Employers where " & GCriteria
Form_Employers.Caption = "Employers (" & cboSearchField.Value & " contains '*" & txtSearchString & "*')"

'Close Search Form
DoCmd.Close acForm, "EmployersSearch"

MsgBox "Results have been filtered."

End If
End Sub


I would like to use the same thing on my 'Jobs' Sub form. How would I change to code have the correct reference? Any help would be greatly appreciated.

Thanks!

[Access_Blaster]

Hi wildthingcg,

You need a "INNER JOIN"

Form_Employers.RecordSource = "select * from Employers where " & GCriteria

This is how I did it a few years back. Substitute your table/ID's for mine

Form_frmSearch.RecordSource = "SELECT * FROM tblWarehouse INNER JOIN tblPurchData ON tblWarehouse.WarehouseID=tblPurchData.WarehouseID WHERE " & GCriteria

Also instead of filtering your form, you could popup a report with the results in preview mode.

DoCmd.OpenReport "rptsearch", acViewPreview, , GCriteria
DoCmd.Maximize

Once you have it sorted out this type of search works great.

Richard

[wildthingcg]

Richard thanks for the reply. I'm still very new to this but if I understand correctly a join only applies to certain fields in the table, right?

Maybe I can explain a little better what I'm trying to do. I have a Main form (Employers) with a Subform (Jobs). I have a command button on the Main form that brings up my search box. When a specific employer is found I want to have another command search button on the Jobs Subform that will allow me to search through jobs records to find a specific record for editing and/or generate a report for that record or all jobs records for that employer.

Can I do this using the inner join?

Thanks again,

Cheryl

[Access_Blaster]

Hi Cheryl,

Can you provide a list of your fields in your main table and Sub table? And are they joined? Or can you up load a copy of the database?


Richard

[wildthingcg]

Hi Richard,

Well my zip file is too large to upload. I could email it if you like.

Cheryl

[wildthingcg]

Hi Richard,

Here you go. Go easy on me. I've never built a DB before and I'm learning on the fly.

Thanks again!

Cheryl

[Access_Blaster]

Hi Cheryl,

I had a quick look at your database this morning. I can see you have put a lot of thought and care into your design. But in my opinion your table structure is not normalized. In your Employee table, you use a phone number as your unique identifier. Whats happen when you need to update the clients phone number? You also have a phone field which duplicates your EmployeeID. You have a primary key called JobCardNumber that repeats in 5-6 tables. Until your database is more normalized your searches and updates will be a nightmare. Take a look a the link below and post back.

http://allenbrowne.com/binary/Access...Chapter_03.pdf


Richard

[wildthingcg]

Hi Richard,

Thanks for the feedback and the link. I'll read it and make my changes and get back with you.

Cheryl

[wildthingcg]

Hi Richard,

Sorry it took so long to get back to you. Hopefully, I've gotten closer to normal. I do have an issue if you look at the Employers table. All of the other tables update properly but Employers table if you look at Employer IDs, 10 and 11 the JobCardNbr (see Jobs table) and EmployerID info are reversed. It should be EmployerID 1 and JobCardNbr 10 and 11. Not sure what I did (or didn't do) to cause that.

Thanks again,
Cheryl

[Access_Blaster]

Hi Cheryl ,

I tried to get your form / subform to filter but was unable. I suspect it has to do with the way its formatted. However I did a quick mock up of your form / subform and had no problems filtering. The answer maybe to start from scratch build the form / subform slowly, and when it filters the way you expect then add the bells and whistles.

Richard

[wildthingcg]

Thanks Richard, I'll try that. I'm going to put it on the back burner though because I have a bigger issue (see my previous reply). I don't suppose you would consider taking a look at it? Then I'll leave you alone, I promise!

Cheryl