Full Version: SELECT Statement in VBA for Combobox on Form...
UtterAccess Forums > Microsoft® Access > Access Forms
Hola, first post.

I have a table, RECIP_INFO, containing only the RECIP_ID and RECIP_NAME fields --the former being the primary key. In the ACTIVITY_LOG table, RECIP_ID is a foreign key, and I have a combobox Lookup (SELECT statement) implemented on that field that populates it with the names from RECIP_INFO using RECIP_NAME.

I already have a form with the RECIP_ID combobox in it, but that form is for adding new information; now I need one to look up information based on recipient name. Other than RECIP_ID, I also require that IN_DATE and IN_TIME be null. Here's an example of the ACTIVITY_LOG and RECIP_INFO table contents, and what I'd like the form to return:

ACTIVITY_LOG (Note that the RECIP_ID is either a 1 or 2 in actuality, but the combobox Lookup allows names to populate the field.)

1 | John | Jane | 3/13/2005 | 4/14/2005 | 12:00 PM
2 | Bob | Harry | 2/14/2005


1 | Jane
2 | Harry


Scenario 1: "Jane" is chosen from the RECIP_ID combobox. No records are returned since IN_DATE and IN_TIME are not null.

Scenario 2: "Harry" is chosen from the RECIP_ID combobox. Record with ACT_ID 2 in ACTIVITY_LOG is returned since RECIP_ID = "Harry" and IN_DATE and IN_TIME are null.

I have no problem creating the combobox; I just need some code to perform the query. I suspect that _Afterupdate will fire the code, but I'm not really sure how to put it together, and have been searching for hours now. I'd greatly appreciate some help with this.

Thank you!
Edited by: bubbisthedog on Wed May 25 15:32:31 EDT 2005.
Create a query JUST from ACTIVITY_LOG. In the IN_DATE and IN_TIME columns set the criteria to Is Null. In the RECIP_ID Column set the criteria to:
Forms!formname!comboboxname Where formname is the name of your form and comboboxname the name of the combo where you select the Recip
The form should be UNBOUND with the combobox UNBOUND. Add a button on the form to run the query. Once you are statisfied that works, then add the RECIP table so you can add the RECIP_NAME to your query instead of the ID.
Perfect! Adds a combobox to a form, and returns the records via ACTIVITY_LOG Query based on the value in the ComboBox. I noticed that Access even automatically added some error-prevention code! GRACIAS!
Add the RECIP_INFO table where? Just a reminder that, although after hitting the button returning the query results in spreadsheet form is fantastic, my main goal is return the results on the same form. So then I'd be able to use the Navigation buttons to flip through records returned by the query. Is this what you're getting at?
Thank you so much for taking the time to help me, Scott!!!
Jerry Dennison
Welcome to UtterAccess!
You should not record to events in the same record. In this particular case you are recording an IN_DATE and an OUT_DATE. These are known as repeating groups (i.e. they are the describing the same attribute). There should only be one activity per record. This means you would have an activity record for IN and then another one for OUT. It is safe to assume that you must come in before you can go out. So every OUT record would have a corresponding (and previous) IN record.
Thanks for the 'welcome!'
Items are outgoing first, then they come back later, just like library books.)
I was initially going to have a IN_OUT field that would contain either "In" or "Out", and then have a DATE and TIME field; but I thought that querying back through records with the IN_DATE and IN_TIME fields null would be simpler. That way, another record wouldn't need to be filled out again with the same information.
Any idears on how to accomplish what I'm after without having two records with nearly identical information? Since this is a log, I didn't think that creating separate tables for 'Out' and 'In' would be a good idear. And I want to keep as normalized as possible.
Thanks so much for the advice, Jerry!
Add it to your query. Right click on the table area on top in query design mode and select Show Tables. Then select the RECIP table and join it on RECIP_ID and then add a RECIP_NAME column.
If you want display the results in a form I would do it a bit differently. I would create a mainform based on the RECIP table and the subform based on the Activity Log query. Link the subform on RECIP_ID. Next use the Combobox wizard to create a Search combo to select Recip on the mainform. Then the subform will show records only for the selected RECIP. Leave the Null criteria in the query, but take out the RECIP_ID criteria.
Thanks for the response, Scott, and sorry that I didn't mention earlier that I wanted the results displayed in a form. Unfortunately, I have a few questions regarding your idear, which I can see conceptually will work, but I'm not too experienced at custom form creation:
) I created the main form for the table RECIP_INFO, including both the RECIP_ID and RECIP_NAME fields. Within that form, I created a subform that includes all fields from the table ACTIVITY_LOG, including RECIP_ID. Was I supposed to include the primary key of RECIP_INFO as well as the foreign key of ACTIVITY_LOG (RECIP_ID, that is)?
2) How do I go about 'linking' the subform on RECIP_ID?
3) I do not know how to create a "Search combo," but I do know how to access and use the combobox wizard.

Oapologize for all the questions; I can only do so much at this point in the game.
Thanks again for your help.
Jerry Dennison
There are two ways to handle this. For a given item/person you would create a single record in an intermediate table for the item/person. Once this relationship and record is established you have a one-to-many relationship between this intermediate table and the transaction table that tracks the "movement" of the item OUT and INto inventory. This method reduces all redunancy between people, items, and movement. Another method is to create a one-to-many relationship between the people and items whereby you will always only check the item OUT to the person. This table creates a many-to-many relationship between people and items while capturing the issuance of the item at the same time. You then create a one-to-one table linked to the many-to-many table where you would capture the return of the item. By creating it as a one-to-one relationship you eliminate "empty" fields within a record and you also eliminate modification of existing records just to add the IN portion of the transaction.
I will test both approaches. Thank you very much for taking the time to provide that information for me. I've always been confused by the construction of 'log' databases, so this is invaluable experience for me. Once again, I really appreciate the input.
Jerry Dennison
You're welcome.
Use the wizards!! They will walk you thru the process. Use the form wizard to create a form based on the Activity log QUERY (not the table). The one that filters out the Nulls. (note, Jerry and I are addressing different parts of this. I would first get the form working, then you can make the changes to conform with normalization).
When place that on the main form. The subform wizard will prompt you for the linking fields. It should be RECIP_ID. The Combobox wizard will walk you thru creating a search combo. just follow the wizard. (not Jerry, the Access wizards wink.gif )
Thanks to both of you wizards for all your help.
!--coloro:blue-->JERRY : I built a new database, dividing the ACTIVITY_LOG into two tables: ITEMS and TRANSACTIONS. The TRANSACTIONS table contains a field that allows either "Out" or "In", and a regular ole DATE and TIME field (as opposed to IN_DATE, IN_TIME, OUT_DATE, OUT_TIME). You were right; this will seriously reduce the amount of data entry required since items is now a separate table. It also 'feels' more like a ralational database structure. However, getting the forms built for that particular database seemed a bit more involved, so, for now , I built the forms around the first DB structure.
SCOTT: After some trial and error, I finally learned how to use the wizards to link the main form and subform, and it works awesome . I created the main form from the RECIP_INFO table, using the RECIP_NAME field for the combobox entries. I then created the subform from ACTIVITY_LOG Query and used the wizard (not Jerry) to link the two forms on RECIP_NAME. It works beautifully, except for one thing:
I have all fields but 2 in the subform locked: IN_DATE and IN_TIME. When I change the name on the main form combobox (RECIP_INFO.RECIP_NAME], and then click in either the IN_DATE or IN_TIME field, the recipient name changes in the first field of the RECIP_INFO database (parent to ACTIVITY_LOG) to the the name selected in the combobox??? That is, if I select John Doe (record 3 in the RECIP_INFO table) from the combobox, and then click in the IN_DATE field, record 1 in RECIP__INFO changes to John Doe as well. If I select Jane Doe (record 5 in the RECIP_INFO table), and then click in the IN_DATE field, record 1 in RECIP__INFO changes to Jane Doe as well. How can I correct this?
I think you missed a step here. You should have created the main form with a text box control bound to RECIP_NAME. In addition you should have used the combobox wizard to create a SEARCH combo. This combo will be unbound and used only to position the main form on the correct record. The subform is then linked to the main form on the RECIP_ID field, so that the subforms only shows records for the current main form record. If you have the RECIP_NAME as a bound column, then changing that changes the record.
Works perfectly now! Lemme ask you this: When you said
I did you mean RECIP_ID? It seems to work fine using either one actually.
ey, thanks so much for your patience and willingness to help me. I've learned a lot from you. yayhandclap.gif
No I meant name. You can have both, but I'm assuming that RECIP_ID is just a Autonumber PK that has no meaning so it can be left off or hidden. The text box will show what record is current. You can either use the search combo to select a record or the record navigators to cycle thru the records.
In the immortal words of Ed McMahon: You are correct, sir! I understand what you're saying now.
Thanks again for all the great help, Scott! I can now use all this knowledge to move forward with other idears.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.