Generate report based on 3 unbound combo boxes search in form

[xaysana]

Hi there,
Please for give me if I post this in wrong forum. But I have to clerify if it is possible to do.

I have been helping HRA creating a single button in a form for click and send record(s) to preview report based on search in form using 3 unbound combo boxes.
Please refer to the attachment for more detail. The frmSearch was what i had stolen from one of your forum. This is very similar to what i am after acept it filters and previews.

What I want to implement is to preview all searched results based on those unbound combo in form.

Could anyone help please?
Thank you for your kindly assistance in advance
Regards,

 

[jzwp22]

In your code, anytime you refer to variables such as the combo box controls, the variable cannot be enclosed within the double quotes otherwise Access will interpret that as a fixed value. Additionally, depending on the data type of the field you are searching, the variable might have to be enclosed by special characters. For numeric fields, no characters are needed. For text fields, the variable has to be enclosed by single quotes and date/time fields must be enclosed by # signs. The 3 activities in your table are all text fields so the variables must be enclosed by single quotes in your code. Also, since you are running the code from the form that holds the variables, you can reference the form with the shorthand me.controlname. Here is the revised section of your code:

'Stores the search criteria and enters it into the Where Condition
If Not IsNull(Me.txtMain) Then
strWhere = "[Main Activity] ='" & Me.txtMain & "' And "
End If

'Stores the search criteria and enters it into the Where Condition
If Not IsNull(Me.txtSecond) Then
strWhere = strWhere & " [Second Activity] ='" & Me.txtSecond & "' AND "
End If

'Stores the search criteria and enters it into the Where Condition
If Not IsNull(Me.txtThird) Then
strWhere = strWhere & "[Third Activity] ='" & Me.txtThird & "' AND "
End If

With that aside, I think you need to take a second look at your table structure before you go too far with your forms. Your table structure is not normalized. If a person can have many activities associated with them, then that describes a one-to-many relationship; that is handled with a structure along these lines


tblPeople
-pkPeopleID primary key, autonumber
-FirstName
-LastName

tblPeopleActivities
-pkPeopleActivityID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkActivityID foreign key to tblActivities


tblActivies
-pkActivityID primary key, autonumber
-txtActivity

The same would be true for your Education and career fields. These should be records in a related table.

Also as a general suggestion, it is generally not recommended to have spaces or special characters in your table and field names.

 

[xaysana]

Thank you for the kind sugguestion, and correcting the codes, really appreciate that. I am too confident if this is what i have been looking for. In fact, codes given is basically for filtering any information relates to an individual record. It appear blank report if the information not match to the record.

What I would like to happen is to: Use those unbound combo boxes to search based on different txt variables then show everything in the report.

Or the codes given might be correct except I have not tried hard enough to apply to what i have been doing.
Could any one help will appreciate please?
Regards,

 

[jzwp22]

Based on the data you have in your database, you will get a blank report because your data is limited at this point. I think it would be better to give your user a message saying that there were no records that met the criteria they selected rather then returning a report with all records--it would be confusing for them. Is that more of what you were looking for?

 

[xaysana]

Hi JZWP22,

Thank you for your reply.
If I understand you right, you mean that shows a report with all records available from the domain (tbl), if it is then I would say no that is not what I have been chasing for.

I can do this in query (refer to an attachment) with codes below:
[Forms]![FormName]![Control]

This works exactly what I would like to do except doing in query does not give a search list. Or is it possible to do pulldown list or dropdown list in query?.

I will appreciate for any help please.
Regards,

 

[jzwp22]

No, it is not possible to use a dropdown within a query, but you can use a form to supply search values to a query which is what your code in the earlier database was designed to do except it showed the data in a report format. You would use the same principle. So I am still unclear of what you are after. Could you explain in more detail what you want to do?

 

[xaysana]

Thank you for your reply and for your being patient with me.
What I would like to do is that:
1. create a simple form contains of 3 unbound combo boxes, each of them link to different variables in table domain and a report preview button. The combo boxes named eg. A) MainActivity B) SecondActivity C) ThirdActivity
2. Those combo boxes store the search values
3. If only A) eg. (Driving) implemented, then it shows only records which meet to the search value
4. If A) and B) eg.(Driving and Labour), then they should appear both values in the report format.

Hope this is more comprehensive.
Thank you so much for your help in advance, i really appreciate for that.
REgards,

 

[jzwp22]

It sounds like you want to be able to add search keywords to the combo box if they are not already there; is that correct?

If so, you can add Visual Basic for Application (VBA) code to the Not in List Event of the combo box that stores the value entered in the combo box. Of course you will need some place to store those values, and the most efficient place to do that is in a table. But I do have a concern with that because users might end up entering some very strange text strings either intentionally or in error. How do you intend to control that?

What might be better is to come up with a standard list of activities and then you (or an administrator) can add to that list as necessary.

Also, I strongly suggest that before you go any further with forms or reports you take another look at the following from one of my earlier posts:

I think you need to take a second look at your table structure before you go too far with your forms. Your table structure is not normalized. If a person can have many activities associated with them, then that describes a one-to-many relationship; that is handled with a structure along these lines


tblPeople
-pkPeopleID primary key, autonumber
-FirstName
-LastName

tblPeopleActivities
-pkPeopleActivityID primary key, autonumber
-fkPeopleID foreign key to tblPeople
-fkActivityID foreign key to tblActivities


tblActivies
-pkActivityID primary key, autonumber
-txtActivity

The same would be true for your Education and career fields. These should be records in a related table.