Question Please help with combo and list boxs for search criteria

[Philman]

Greetings,

First I would like to say that I'm portuguese and apologize for my bad english.

So, I'm a begginer in Access and I'm making my first database, but I got stuck. I need to put in a form a way to do record searches based in some criteria. Given my database structure, I really don't know the best way to do that, but I was thinking in setting up a few comboboxs to filter some criteria and show the results in a few listboxs. Perhaps for each criteria 1 combobox and 1 listbox, and the same for other criteria.
Well, for better understanding, I uploaded to here my database. It's a poor database, but don't forget I'm a begginer. For what I need this is enough.

I would be very gratefull if someone could take a look and help me solve this, so I can move forward.

Quick explanation:
- the file is in edit mode
- when you open it should go to a navigation panel
- there we have:
- "New Contractor" that leads to registration form
- "Update Contractor" that leads to a form for making changes
- "Contractor Overview" that leads to a form for getting information
- "Search Criteria" that leads to a form where I want to make searches based in combobox and list the results in listboxs. The combo and listboxs are already designed and sorted, the problem is that I dont know how to put them working properly. I'm sure when you see them you will know what I'm looking for them to do.

It's important to mention that there are 4 tables that are related to eachother through a secondary key.

I wich I could explain better, but my lack of english doesn´t let me.

This is very important for me, because my job needs this database, so I'm counting on you to give me a hand.

Thank you very much!
Philman

 

[jdraw]

I recommend you work on your tables and relationships to establish a meaningful data model ( and referential integrity) before getting too deeply into forms erc.

Your combo boxes should not have duplicate values. There is a free video at
http://www.datapigtechnologies.com/flashfiles/combobox1.html

Good luck with your project

 

[Philman]

I recommend you work on your tables and relationships to establish a meaningful data model ( and referential integrity) before getting too deeply into forms erc.



Good luck with your project

Thank you jdraw for your tip.
So, do you think I won't be able to filter the information as I want with the existing tables and relationships?
Case not, could you please give me some guidelines of how would you build the tables and relationships, taking in consideration the needed fields to record the data?

Thank you once more.

 

[jdraw]

I don't see any relationships. When I look under database tools Relationships -- nothing.

Am I missing something?
I wasn't commenting on your filters. I see no reason why you can't filter your data based on combos.

 

[Philman]

I don't see any relationships. When I look under database tools Relationships -- nothing.

Am I missing something?
I wasn't commenting on your filters. I see no reason why you can't filter your data based on combos.

You are right, they don't appear, but they are there. Please, when looking under database tools Relationships, try to hit the tool "all relations", right next to "show table".

Please, give me a hand to solve this.

Thank you very much.

 

[jdraw]

OK I see them. I think your relationships are incorrect. But I don't know your intentions nor your data.


Skills---->ContractorSkills<---Contractor-->ContractorLang<---OLang

I think you would identify Team --made up of--> Contractors
even the Contract -- Forums would be separated out

Take a look at his article
http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[Philman]

OK I see them. I think your relationships are incorrect. But I don't know your intentions nor your data.


Skills---->ContractorSkills<---Contractor-->ContractorLang<---OLang

I think you would identify Team --made up of--> Contractors
even the Contract -- Forums would be separated out

Thank you jdraw for your patience with me.
As a begginer, I can't see other way to do the relationships. However, for the purpose, I think they are ok. Correct me if I'm wrong!

Still, I'm not being able to set up the filter results as I wish, and this is very important to my work.
Please, let me try to explain better the required information, the tables and its relationship, to see if you can give me a hand.

So, I have a form where I want to register new contractors. This form has some fields where I will be typing the required data. For each new contractor, a line is created in a table. This form has also 3 subforms to register other information about each contractor. Each subform has a table where other information of each contractor is recorded. The 3 tables of these subforms have relation with the main table through a secondary key. You can see this form by opening the file I uploaded here, and in the Navigation Panel, go to "New Contractor". So, 1 form with 3 subforms in it, registering data in 4 diferent tables.

Decomposing explanation...

tbl_Contractor has these fields:
-ID (unique number)
-Name
-Native Language
-Status
-Hourly Rate
-Email
-Skype
-Membership
-Forum
-Comments
-Best past job
-Url best past job
-Short description best past job
For each contractor I register, I type the data in these fields, that is recorded in one line in the table. For each new contractor, a new line with his data is created.

tbl_OtherLanguage has these fields:
-ID (unique number and not shown in the form)
-Other languages
-ID Contractor (not shown in the form. This is the secondary key and it has a relation with the field ID on the tbl_Contractor). This appears in the register form as a subform. This is to register the other languages that each contractor is able to speak. One language per line. In other words, each contractor, besides his native language, can speak many diferent languages.

tbl_Teams has these fields:
-ID (unique number and not shown in the form)
-Teams
-ID Contractor (not shown in the form. This is the secondary key and it has a relation with the field ID on the tbl_Contractor). This appears in the register form as a subform. This is to register the teams that each contractor belongs. One team per line. In other words, each contractor, can belong to a few diferent teams.

tbl_skils has these fields:
-ID (unique number and not shown in the form)
-Skills
-ID Contractor (not shown in the form. This is the secondary key and it has a relation with the field ID on the tbl_Contractor). This appears in the register form as a subform. This is to register the skills that each contractor has. One skill per line. In other words, each contractor, has many diferent skills.

So, let's imagine I'm going to register a contractor. For that, I go to the form "New Contractor" and add his information. For example:

Alexander, is a native "Italian", his status can be "Active" or "Inactive", never the both, he has an hourly rate of "10 USD", his email is "someone@domaincom", his skype is "someone.alex", his membership can be "New" or "confirmed", never the both, his forum is "registered" or "or not registered", never the both, his comments are "whatever", his best past job is "wordlocker", his url for past job is "wwwsomewhere", and his short description of best past job is "Great writer". (so, all this data is registered in one single line through the "NewContractor" form in the tbl_contractor);
However, Alexander, besides is native language, speaks "German", "English" and "French", (so, this data is registered through the subform "OtherLanguages" inputed in the "NewContractor" form as well and recorded in the tbl_OtherLanguage. One language per line);
Still, Alexander, belongs to the teams "writing&translation" and "Data Entry", (so, this data is registered through the subform "teams" inputed in the "NewContractor" form as well and recorded in the tbl_OtherLanguage. One language per line);
In Addition, Alexander, has many skills, like "ghostwriter", "article writer", etc., (so, this data is registered through the subform "Skills" inputed in the "NewContractor" form as well and recorded in the tbl_skills. One language per line).

I can do all these data registration in their respective tables without any problem. The problem, is to setup search criterias with combobox and listboxs. For this, I've created a form named "Search Criteria", in it, I putted one combobox and a listbox for each search criteria. If you go there, you will see a few combo and listboxs to etup each criteria.

What I need:
1 combobox that has the membership criteria, "Confirmed" or "New", recorded in tbl_contractor table. When I select one of these 2 criterias, I would like that the imediate below listbox tell me the name of the contractors that fills that criteria.
The same for other comboboxes and listboxs.
The rule is: the combobox lists the criteria to search and the listbox gives me the names of the contractors that fills that criteria.
Finally, after having in the listbox the names that fills the criteria selected in the combobox, I would like that when I click in any name, it leads me to the form "ContractorOverview" in his own registry, so I can see his complete profile.

jdraw, I know you have more important things to do instead of helping a guy you don't know, but I beg you to help me solve this issue. It's really important to me and I have no one else to ask for help.

Please, give me a hand with this.

Thank you very much.
Philman

 

[jdraw]

I would not set up the tables the way you have them - but it depends on what your Business rules are.

-Status
-Hourly Rate
-Email
-Skype
-Membership
-Forum

all relate to "as of today or some date". You have no way to maintain any history of these attributes - but maybe you don't want to.
I would have a table of Skills, and junction table of ContractorHasSkill.
You can see more on resolving Many to Many at
https://www.youtube.com/watch?v=7XstSSyG8fw

I recommend you write down your Business facts and
work to get the data model designed to meet your Business facts before getting too involved with forms.

Most of the questions and issues on these forums relate to tables and relationships. If your underlying model does not match or support your requirements, you will spend a lot of time trying to create work arounds for a structure problem.

Don't throw anything away. Just get the model confirmed/adjusted. And test the model with some sample data. Make sure you can get the info out and in to your database to meet your needs.

You show "New" or "Confirmed", not knowing your set up , I see these as different levels. I would think "New" or "Existing", and "Confirmed" or "Not Confirmed" would be terms.

Good luck with your project.

 

[Philman]

I would not set up the tables the way you have them - but it depends on what your Business rules are.
all relate to "as of today or some date". You have no way to maintain any history of these attributes - but maybe you don't want to.
I would have a table of Skills, and junction table of ContractorHasSkill.


I recommend you write down your Business facts and
work to get the data model designed to meet your Business facts before getting too involved with forms.

Most of the questions and issues on these forums relate to tables and relationships. If your underlying model does not match or support your requirements, you will spend a lot of time trying to create work arounds for a structure problem.

Don't throw anything away. Just get the model confirmed/adjusted. And test the model with some sample data. Make sure you can get the info out and in to your database to meet your needs.

You show "New" or "Confirmed", not knowing your set up , I see these as different levels. I would think "New" or "Existing", and "Confirmed" or "Not Confirmed" would be terms.

Good luck with your project.

Thanks jdraw for the tips. You are a good guy!

For my needs I don't need date for the records.

My model of structure is enough for my needs and I think it's well designed for the purpose. I'm sure it could be better, but I don't know how to do it. So, it has to be like it is. I think the relationship is not the problem for me to get the filter results I want. I hope not being wrong! Perhaps it's only a matter of properties, I don't know.

I can't see the benefit of taking off the below fields of the same table as Name, Native Language, etc.
-Status
-Hourly Rate
-Email
-Skype
-Membership
-Forum

"New" or "Existing", and "Confirmed" or "Not Confirmed" are only terms to type when registering a new contractor. These terms can change as time goes by, but each contractor can't have the both terms.

I've read many articles and seen some videos, but I still can't get there.

Have you tried to see if you can set up one of the combos and list box to give the results I need?
If you could do so, I would be very greatfull, otherwise, I think I will give up and forget the database.

Thank you very much.
Philman

 

[Daz]

Thanks for the link on the combo box it cleared a lot up for me Daz

 

[jdraw]

I am attaching a demo database.
3 tables related to Animals, AnimalCapture and AnimalLocations.

1 form with
1 Combo to select an Animal by Name and
1 Listbox to show LocationInfo for the Animal Selected


In design view of the Form,
under Data, look at the Rowsource
under Event look at the after update
Good luck,

 

[Philman]

I am attaching a demo database.
3 tables related to Animals, AnimalCapture and AnimalLocations.

1 form with
1 Combo to select an Animal by Name and
1 Listbox to show LocationInfo for the Animal Selected


In design view of the Form,
under Data, look at the Rowsource
under Event look at the after update
Good luck,

Hi jdraw,

Thank you for the sample file.
In fact, I was tired to use in my database the same expression as your sample, but it doesn't wants to work.
Finally, I've got to the conclusion that, having the DB as I do, my search criteria can't be done by access features, it has to be done by VBA code. At least, is this the way I think.
So, I found in the Internet a code that put my desired search criteria to work, however, it doesn't do all the process I wish.
Maybe you can help me with that.
Reminding my goal:
I have one combobox that is getting the data from a column in a table. This is the search criteria. When I select one of the shown data in the combo, it will give me the result that fits that criteria in a listbox. Until here, the code is working good. But to finish the desired process, I want when double-clicking in a result of the listbox, it opens another form in the same registry ID.
Example:
Combobox gives the search criteria "A", "B", "C"
When I choose the criteria "B", the listbox gives me the names that fits that criteria, for example, "Alex" and "John".
So, I want to double click in Alex and then be redirected to a form that has all Alex's info. With the code I have I can select the name and it redirects to the form perfectly, but it goes to the first registry not to the one that belongs to Alex.

Now the code I have ( the above criteria and names are only examples, not the ones in the code):

Combobox/properties/Row Source

SELECT Contractor.membership FROM Contractor GROUP BY Contractor.membership ORDER BY Contractor.membership;

Combobox/properties/onclick event

Private Sub Combo53_Click()
Dim rss As DAO.Recordset
ssql = "Select * From Contractor Where membership = '" & Combo53.Value & "'"
Set rss = CurrentDb.OpenRecordset(ssql)
'For i = 1 To r_membership.ListCount
'r_membership.RemoveItem i
'DoEvents
'Next
Dim indc As Integer
indc = 0
List55.RowSource = ""
If rss.RecordCount > 0 Then
rss.MoveFirst
r_mem = "" '"'" & rss.Fields("name").Value & "';"
Do While Not rss.EOF = True
'r_mem = r_mem & "'" & rss.Fields("name").Value & "';"
'indc = rss.Fields("id").Value
List55.AddItem rss.Fields("id").Value & ";" & rss.Fields("name").Value
rss.MoveNext
indc = indc + 1
DoEvents
Loop
rss.MoveFirst
End If
'List55.RowSource = r_mem
End Sub

I don't have any idea why are some rows with apostrophe, but it works.

Listbox/Properties/

there's no row source, it only has the kind of row source setted to list of values

Listbox/properties/on double click event

Private Sub List55_DblClick(Cancel As Integer)
'MsgBox List55.Column(0)
'fltr = List55.Column(0)
'Dim rss As DAO.Recordset
'ssql = "Select * From Contractor Where membership = '" & Combo53.Value & "'"
'Set rss = CurrentDb.OpenRecordset(ssql)
'set form["Contractor Overview"].
DoCmd.OpenForm "Update Contractor", , , , , , "id = " & fltr

End Sub

I don't have any idea why are some rows with apostrophe, but it works and it goes to desired form, but not to the registry of the ID I selected.


Do you have any clue what's missing here?

Thank you,
Philman

 

[jdraw]

The apostrophes make that line a comment.

I've added a dbl click to the listbox item

See if that helps

 

[Philman]

The apostrophes make that line a comment.

I've added a dbl click to the listbox item

See if that helps

Yes, jdraw, that line you added in your example is exactly what was missing in my code.
Now it works as expected.

Now, for those who need a similar code, just check the one I've posted above.
In the last line of Listbox/properties/on double click event
change the line:
DoCmd.OpenForm "Update Contractor", , , , , , "id = " & fltr
to this one:
DoCmd.OpenForm "update contractor", acNormal, , "ID = " & Me.List55.Value
and adapt the fields to your own database.

Thank you very much jdraw, you are the best.