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 programmaticall y 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 misunderstandin g what you are describing to me.

[beacon]

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.

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]

Pictures of database

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]

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.

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.