Access 2003 - Combo Box Query Question

[Chad Brewer]

I have a form that has two tabs. One is called New Request and the second tab is called Follow Up. Each of these tabs have fields on it that come from a query that has linked two tables (New Request table and Follow up Table) using the primary key on both tables as "Tracking." The "Tracking" field is a text box that is input by a user on the first tab (New Request) on the form.

I would like the user to go to the second tab and select a combo box that shows all available "Tracking" values that the user has input. Then when they have selected one of these values, the second tab refreshes and displays the fields for the user to input in regards to the Follow Up table. I'm having trouble doing this with a form that has a record source of the query. Help would be much appreciated as I've been looking everywhere for this answer. It seems like it would be simple. I'm kind of new to Access so VBA is hard for me. Thanks!

[beacon]

Hi Chad,

Welcome to Bytes!

Do you have subforms on the tabs? I recently did something similar, but I was using a combo box in conjunction with a subform to filter/requery the subform. However, I was trying to get the subform to filter based on info on the main form. Are you trying to get everything on the form related to the New Request AND the Follow Up to requery when the combo box changes?

Here's how mine was setup to give you an idea that you may be able to tweak to modify your form based on what you're trying to accomplish.

My main form, and the info on tab 1, shows general information for a patient. The info on tab 2 is for additional information for the patient and is in a subform with a different query. There is a combo box on the 2nd tab (not in the subform) that the user can click to requery the subform. So if the user changes the combo box value to "Admit" the subform will requery and only show "Admit" info...if the user changes the combo box to "Discharge" the subform will requery and only show "Discharge" info.

I created a combo box, called cboAudit, and placed it on the 2nd tab. I entered the following code in the AfterUpdate event:

Private Sub cboAudit_AfterUpdate()
 
    Dim varAudit As String
 
    'AuditStage is a field on my subform that I'm using for comparison with the value in the cboAudit combo box
    varAuditStage = "SELECT * FROM qryEpisodeDetailOSC WHERE AuditStage = '" & Me.SubFilter.Value & "'"
 
    Form_fSubFormEpisodeDetail.RecordSource = varAuditStage
 
End Sub
 

[Chad Brewer]

Thanks for the reply! I was using a subform and it did work on displaying the follow up fields for the "Tracking" number that was selected in the combo box. The problem I ran into is this - One of the fields on the subform is called "Status." When the user inputs the "Status" as "Closed," I want the combo box not to show that "Tracking" number anymore. I attempted to put the criteria for "Status" in the Follow up query (this is the record source of the subform) however I couldn't get the combo box to refresh. Will the code that you provided do that? Again I'm sorry, code is very foreign to me and thanks again for taking the time to help me.

[beacon]

Is the Tracking number combo box on the subform or is it on the tab control? The answser will kind of change the code below, so I'm going to assume that the combo box is also in the subform. If it's different and you have trouble modifying the code, let me know.

I think you can create a query that shows only the records that have "Status" not equal to "Closed." To make things easy, I'm going to refer to this query as qryStatus, although you can name it whatever you want (just be sure to change it in the code below). I would set the row source for the combo box to nothing that way you can programmatically set it when the "Status" value changes.

In the BeforeUpdate event for the "Status" field, you can use the following code:

If IsNull(Me.Status) then
     Me.TrackingCombo.RowSource = ""
Else
     Me.TrackingCombo.RowSource = "qryStatus"
End If
 

This should work given that my assumptions that I mentioned at the beginning are correct.

[Chad Brewer]

Again I hope I don't seem hopeless. I'm attempting to do what you said of having the combo box only on the subform. Now when I enter the CAR Number (which is the "Tracking" field in my tables and query)on the first tab of the main form (main form's record source is the table "New CAR") and click save, then go to the follow up tab that has the follow up subform (record source Followup_qry [query is joining the "Tracking" field between "New CAR" table and "Follow" table]and this where the combo box is) there is nothing displayed in the combobox to select.

The only way I get something in the combo box is if I make the combo box on the main form but still under the tab "Follow up" with the row source = SELECT [New CAR_tbl].Tracking FROM [New CAR_tbl]; Then the CAR number shows up and as soon as I enter anything into the subform it makes that link with the Followup_qry. But then the problem exists with removing that CAR Number from the combo box when the user selects closed on the subform.

Hope that makes sense.

[Michael Adams]

Where is the row source pulling its data from on the combo box? I just finished one (combobox) that combines two different fields from a table. You could use this code to combine as many fields as you wish into a query, then use this query to populate your combo box.

query field name: table.field & "" & [table]

Put this in your new query that will be the source for the combo box. It goes in the Field row of your query.

This code pulls data from table.field and a table with only one field theus the [].

Hope this helps.

[Chad Brewer]

Thanks for the reply Michael. I'm not sure how that will help my problem of being able to have a combo box whose values are dependant on what is selected in a subform with a different record source than what the record source for the combo box is.

It's very possible I'm misunderstanding what you are describing to me.

[beacon]

@Chad Brewer
Do you capture who the user is in your table when a new request is entered? If not, you'll need some way to capture who the user is and then you can change the row source for the combo box to:

"SELECT [New CAR_tbl].Tracking FROM [New CAR_tbl] WHERE [User] = '" & YourUserFieldHere & "';" 
 

Not promising this will work, but it might. If it doesn't, can you take a screen shot of your form and post it?

[Chad Brewer]

This is a static database meaning it's intended for one user only at one time. No need for multiple users. I tried to do a screen shot in my last reply however I couldn't figure out how to post it on this board (won't let me paste). The only thing I see is a url and I'm using snagit to capture the shot. I'm positive once you see the form and the related queries it will help clarify things. Any ideas on how to get the screenshot up? Just to clarify, my only problem (which maybe a big one) is - I have to figure out a way to have a combo box that is based on a value (Tracking field) in a query (The query is linked between two tables). Then whatever the user selects into that combobox, have the subform that is located on the tab on the form be linked to whatever Tracking number the user selects in the comboform. The reason I want this in this format is so that when the user selects the status as "Closed" on the subform, it would remove that tracking number from the particular "Tracking" combo box.

[beacon]

Before you post your reply, instead of clicking on "Post Reply" or "Post Quick Reply," click on "Go Advanced" and you can upload an attachment (so long as it's within the size limits of the forum).

I've got a pretty good idea what you're trying to do...I think we're just missing the mark on something, but I'm confident we can figure this out! =)

Just to make sure we're on the same page, you have a combo box for Tracking and a combo box for Status. The Tracking combo box is on the main form and the Status combo box is on the subform.

With that in mind, if it's correct, we should refresh the Tracking combo box whenever it takes the focus. We should also put code in the BeforeUpdate event for the Tracking combo box to requery the subform when a value is selected.

I would create a query that has an expression in it that only returns the Tracking numbers that aren't closed (put Is Null in the Criteria). Then put this query in the row source for the Tracking combo box.

Now, let's try this on for size for when the Tracking combo box gets the focus:

Private Sub Tracking_GotFocus()
 
    Me.Tracking.Requery
 
End Sub
 

You will want to remove the Record Source for the subform because we are going to set it dynamically in the BeforeUpdate event of the Tracking combo box.

Now, let's setup the BeforeUpdate event for the Tracking combo box:

Private Sub Tracking_BeforeUpdate(Cancel as Integer)
 
    Dim strSQL as String
 
    strSQL = "SELECT [Tracking] FROM [New CAR_tbl] WHERE [Tracking] = " & Me.Tracking.Value & ";"  'This assumes that the value in the Tracking combo box is a number. If it's a string, you'll need to put a single quote after the equal sign and another one before the semicolon.
 
    Forms_YourSubFormNameHere.RecordSource = strSQL
 
End Sub
 

We shouldn't have to do anything with the Status combo box in the subform because the GotFocus event of the Tracking combo box should handle refreshing the values.

[Chad Brewer]

Thanks. I will try what you suggested regarding the coding. In the meantime, I will post the snagit shots that I have. This should help make it more clear. And I agree and believe I'm probably missing something small to make this work because theoritically it doesn't sound hard to do.

[Chad Brewer]

Here are the 3 shots pertinent to our discussion. The first should be a shot of the main form on the "New CAR" tab. The second is the query that drives the subform on the second tab. And the last picture is the shot of that second tab, "Follow Up." Hopefully this helps. Let me know if the pictures don't upload correctly.

[beacon]

@Chad Brewer
I'll take a look at them, but will hold off on replying until I hear back from you about whether or not my previous post worked for you or not.

[Chad Brewer]

Unfortunately this didn't work. When I enter a New CAR on the first tab and then click save, then go to the second tab to locate that new CAR that I just entered, it's not in the combo box. To help, the tracking number is set as a Text field and the Status field is just a drop down (i.e. value list). So hopefully the images will help.

[beacon]

Did you create a query for the CAR combo box that only returns tracking numbers where the Status is null? I was calling this combo box the Tracking combo box in my earlier posts, but I'll call it by the name I see in your screen shots.

In your screen shot, you say that the combo box has a 'record source' of the 'New_CAR_tbl'. Do you mean that the combo box has a 'row source' of 'New_CAR_tbl'? If so, you should replace the row source for this combo box with a query that only returns the tracking numbers that have a Status that is null. As long as the combo box has a row source set to a table, the values in the combo box won't update (unless you update the table, which is not the direction I think you want to take a for a single combo box).

Once you get the row source for the CAR combo box changed, try the other stuff I provided in the previous post and see if that won't work for you too.

[Chad Brewer]

So you are suggesting that instead of having the CAR ("Tracking) combo box that is located on the "Follow Up" tab on the main form, put the combo box on the subform, since the subform is already a query. The other thing I want to make sure is the "is null" part. Status is not a text box but a value list, meaning the user can select three options - Awaiting ERC response, In progress, and Closed. So I'll try setting the criteria to only show Awaiting ERC response and In progress, then make the combo box on the subform and then try your code you suggested.

[Chad Brewer]

So before I do what I sugessted in my immediate reply (i.e. putting the combobox on the subform) I thought I would try again what you suggested in the first place. So I used the same query that is the same query as my subform and set the query criteria as <>"Closed" (see attachement 1). I set the record source of the subform (FollowupForm) to nothing. Then I went to the design view and selected the combobox (name of the combobox is Combo59) that is located on the "Follow Up" tab of the main form. I set the row source type to Table/Query and then the row source to "Followup_qry" then I input the code (see attachement 2). So then I tried it out - I went to the first tab on the main form and input a new car number and then clicked save. Then went to the combo box on the second tab (Follow up) to see if it was there and the combo box is blank (attachment 3). So with all that said I'm confident we are narrowing it down. Hopefully these attachments will help out too. Also if it matters - after I filled out the first tab on the main form, I closed the form and went directly to the Followup_qry to see if that CAR number was in the query and it wasn't.

[Chad Brewer]

Anyone there?

[Michael Adams]

Ok, I just took a look at your images and this is what I can suggest to make this very easy.
1. Create a table with the "employee" and "Car"
2. Link that table to your sub form and next to car have employee combo box
3. That way you can get the info from that table Unless you have a master table with alll the field from every form/sub form.

Questions:
1. Do you have a master table with all info?
2. On the first tab do you have a spot for the employee to select him/her self?

Thanks,

Mike

[Chad Brewer]

Hi and thanks for the reply. I'm not sure which attachments you looked at. I don't have a field name "Employee" nor do I need one, so that is why I'm confused to your recommendation. See post 17 (I think) for where I'm at in this process. Those attachments show my table, query and forms.

[Michael Adams]

On the new car tab, when one enters the car number and clicks save what field does that number populate?

[Chad Brewer]

When the user manually enters a text (i.e. 2010-001) to represent the new CAR number and hits save, that field is the "Tracking" field located on the New CAR_tbl.

[Michael Adams]

After you enter that number Your problem arises when you go to the second form and that new number is not there, correct?

[Chad Brewer]

Sorry about the delay. I'm swamped at work. But yes to an extent. I want to have the new CAR number displayed in that combo box on the second tab after they enter it on the first tab of the form, but also have the new CAR number disapear after the user selects "Closed" in the Status field of the subform.

[Michael Adams]

And the new number is being written to the main table DB? And the second form new car field is being populated by the main table DB?

[Chad Brewer]

Do you mean the second tab instead of "And the second form..."? IF so, then no. The "Tracking" field on the second tab (labeled just as "CAR:") is currently has a row source of the "Followup_qry"

[Michael Adams]

When you click "save" on the first form what does it do? Have the car combo box pull from the field and table where you wrote it to on the first form.

[Chad Brewer]

I did that earlier and it does work but the problem comes into play that I need that combo box to not show the CAR if the CAR has been "Closed." That is why I need the combo box to be tied to a query. When you click save it just clears the information in the first tab so the user can enter another CAR such as "2010-001" followed by "2010-002"

[Michael Adams]

Make an if statement that looks for the "closed" field then have a message box say something like "This car service has been closed. Please select another car."

That way you don't need the query.

[Michael Adams]

Is it true that when you close the first tab of your form and go to the second tab the car you just entered is not showing up?