I have an Access DB to store CV information. At the core of it are 4 tables: 1 parent table (Personnel) and 3 child tables (WorkExp, Education, & Language).
I have created a search form with VB code attached to the "Search" button. The search form has a number of fields for keyword entry / type selections, but so far I have only been successful at getting the 1st keyword field to search properly (this is a "Name" keyword and only looks up the parent table). The VB script essentially is aimed at generating the appropriate "WHERE" string (SQL) to select the appropriate records.
1. I am having trouble getting the search to work with more than one keyword field. I guess I need to be using some sort of If-Then-Else loop tree to get the correct "WHERE" syntax.... any suggestions.
2. This is made more difficult as I need to search the 3 child tables as well from the one click. If I only had the 1 parent and 1 child, the SQL syntax would be simpler to generate.... any suggestions.
This is my first "less-than-simple" try at VB, so there are still some concepts and syntax I have still to get my head around. I have attached my code as it reads so far. Greatly appreciate any help you can give.
I had a look over your post and hopefully we can get get you at least heading in the right direction again. Since you say this is one of your first forays into VB (brave to start out with DAO recordsets!), i'll pop in some general advice along the way.
Reading through your code:
Default value of a string is a zero length string (""), so no need to explicitly assign it this value (0 for numeric, False for boolean...).
You might find positive evaluations (if Len(me!namekey)>0 then) easier to read than negative ones (if not Len(me!namekey)=0 then) . Alright, I mean I do!
For the Where condition, Chr$(34) is ". I think you were after Chr$(39) which is '(apostrophe).
You are probably right - a check to see if there is a value in the control before adding it to the condition would be good. In addition, as it stands, if you enter "AChineseName" for ChineseName and nothing else, the where condition works out equivelent (but not identical!) to:
"where GivenName is not null OR Surname is not null OR NameChinese = '*AChineseName*'".
This would pretty well bring up all your records. You either want to replace the ORs with ANDs or test the value of the control (with an if..then) and only include the condition if, for example, len(me!namekey)>0.
You might find msgbox strFilterPers or debug.print strFilterPers after you have created the where statement would be useful to check it's syntax - it is easier to spot errors (like the Chr$(34)) if you see what VB does with your code. While we're on this topic, you could do worse than read up about VB debugging tools like breakpoints, stepping through code, watches, immediate window etc.
Ok, if you try the above hopefully point one is covered. I would get this working fine before moving on.
For point two -
It would be difficult if you were going to write a SQL staement with four tables and three outer joins, but you're not going to! Create a query in design view, bring in your four tables, create the three necessary (probably outer) joins from the parent to the, er, children. Put in all the fields you need but no where conditions. Go to SQL view, copy and paste the SQL into your recordset to replace the current SELECT statement. Not sure what anyone else thinks, but my feeling is that writing SQL is an important skill, but once you have more than three or so tables you are just making things difficult for yourself- let Access work out the join syntax for you.
Anyhoo, once you've got all of the above working you can make one long filter string to test in one SQL statement instead of four strings to test in four statements.
One final thing - is all this to test if there will be records when the form opens? if so, why not open the form and, if there are no records, send out your message boxes and close the form down again? This would perform the same function with less processing. A thought, anyway.
Hope this gives you a few pointers on where you can play around!