I am pretty new to VB, and am trying to create a search that retrieves the PersonnelID field from various tables and adds them to a Search table.
I can't get new records added to the search table using the VB on a button below:
'Search based on the strings we have built
Set db = CurrentDb
Set rcdFinal = db.OpenRecordset("SELECT Search.ID FROM Search", dbOpenDynaset)
'Do any people Personnel records satisfy the WHERE clauses?
Set rcdPer = db.OpenRecordset("SELECT DISTINCTROW Personnel.PersonnelID, Personnel.GivenNames FROM Personnel WHERE " & strFilterPers, dbOpenDynaset)
Do While Not rcdPer.EOF
rcdFinal!ID = rcdPer!PersonnelID
I have also attached the full VB script for those who are interested.
Any help would be appreciated.
Last edited by Trumpet; 07-03-04 at 22:29.
Reason: forgot to attach attachment
At a quick glance, you're missing the Update method.
Do While Not rcdPer.EOF
rcdFinal!ID = rcdPer!PersonnelID rcdFinal.Update
Also, consider using rcdFinal("ID") instead of rcdFinal!ID
As you get better with coding, you'll find it more useful. The value in the () can either be a string that explicitly names the field, or an integer, which reference the field by it's index (field indexes in DAO recordsets start at 0).
So, if you have 15 fields, you can make a loop and use a variable instead of having to type out each field name.
Thanks for that - now the Search table updates. There is an abnormality though. When I click the search button it asks for a value for the Table!Search!ID field. Doesn't seem to matter what I enter as the results seem to be stored in the Search table correctly.
Thanks for all your help. I solved the problem. There was some code later in the sub that openned the search results form - this was too complex so I simplified it and made the 'Search' table part of the search results form's query. Problem solved.
The code you have seen is the basis for a search that will eventually lookup 1. the parent table (Personnel) and 2. 3 seperate child tables (WorkExp, Education, and Language). I found using your method worked with 1 parent and 1 child only.
I confess to be new to VB, but the best way I have found to resolve the search of 1 parent to multiple child tables was to have the parent's ID stored in this 'Search' table and used to create the search results form. Also, I hope to be able to 'weight' search results later (as I am using multiple search criteria as well), so that if an ID is recalled more than once a counter will increase - the final results being sorted by the counter.
I haven't been able to find anything about this sort of searching - so if you think there are better ways I would be greatful to hear your thoughts.
To answer the "Me" question, VB is "Object Oriented Programming". Things are "Objects" or "Containers".
Each Object has properties, methods and actions. A container has these as well, but also contains Objects of it's own (therefore, most things are actually Containers).
You will also hear about "Collections" from time to time. A collection is a set of matching objects. A form, for example, has a collection of controls - each control on the form is in that collection.
For example, each Form is a Container, that is to say, it is an Object and also has it's own set of Objects. The form has "Properties" such as "FormName", and "Actions" such as "Requery" and "Methods" which include arguments. It also has objects, namely the controls you place on it.
Each control is an object. A Text Box is an Object that has it's own assortment of properties, methods and actions.
When you type "Me" you are referencing the container you are in - generally this is used in code behind a form. It's nice, becasue you can move code from one form to another easily. Anyway, when you use "Me", it's the same as explicitly referencing the Form Object, namely Forms!YourForm!YourControl.
So, why the period instead of the exclamation point you ask? Open a form in design veiw and switch into code view. Now, type Me. and a little "combo box list" appears with all of the objects, methods, properties and actions available for the form. You can type the first few letters, page up and down, arrow up and down and tab when you find a value you like. Choose a control name on the form and hit the period again and a new list apprears - the objects, methods, properties and actions available for the control. This happens when you follow any object directly with a period.
It makes coding more accurate and faster! For more detail, I suggest visiting a good book store and browsing the computer books until you find one appropiate for your level (perhaps a bit above your level so it remains useful for a while!). You can also search the help file for "Help on VB"