Two questions dealing with queries and radio buttons

[GoodOmens]

Hi,



I am setting up a very simple access form that fires off one report (of two) based on user input. The reports are generated from sharepoint and are already working fine. I'm just having a little trouble streamlining the selection form.


Ideally I would like this scenario:

The user opens the program and sees a form with the following options: A grayed out date selection box, two unselected radio buttons and a "Submit" button.

The user clicks on a radio button and selection box generates a week range based on the radio box selected. He then clicks submit and fires off the report.

I already have this semi-working. What I'm having trouble with:

1)No matter what default value I select for the radio box group(I've tried blank, null, and a random value), the first value is always selected. Is there a way for me to have no default value selected? I do this because the "after update" event ungrays the week selection box. Since the first radio box is already selected, the user has to click on the other radio box and click back. Kinda confusing for users.

2) Right now for the week selection box I have a hard coded query that pulls the weeks from the first report (They are stored in separate tables). IE:

SELECT [Weeks].[Week] FROM Weeks ORDER BY [Week] DESC;

What I would like would be the option if radio box 1 is selected, pull from :

SELECT [Weeks1].[Week] FROM Weeks1 ORDER BY [Week] DESC;

If Radio box 2 is selected pull from:

SELECT [Weeks2].[Week] FROM Weeks2 ORDER BY [Week] DESC;

Is there any easy way to do this? I know I could just union the two weeks - but there are times when a week doesn't have a particular report so I only want to have weeks shown that have reports.

Thanks!

[TG_W]

For Question 1) - Have you tried setting the value to null when the form loads?
For Question 2) - Have you tried an If...Then kicked off on After Update?

[GoodOmens]

For Question 1) - Have you tried setting the value to null when the form loads?

That's what was causing it seems. I thought setting the "default" value would reset things on form load but I guess that was not the case. That solved that problem!

For Question 2) - Have you tried an If...Then kicked off on After Update?

Ah cool. I didn't realize you could update the RowSource with a string. Got it working!

Thanks for such a quick response.

[TG_W]

Sorry, I think I had where combo boxes are backwards on my intial understanding. If what you want is to change the combo box, you can create 2 different boxes, place them over one another, then show the one you want based on the radio box selected (On click).

Private Sub Radio Box 1_Click()
        ComboBox1.Visible = True
        ComboBox2.Visible = False
End Sub

Private Sub Radio Box 2_Click()
        ComboBox1.Visible = False
        ComboBox2.Visible = True
End Sub

Hope that makes sense and helps.

[TG_W]

Oh, ok. Cool. Glad I could help!

[GoodOmens]

Oh, ok. Cool. Glad I could help!

Yep thanks again - one last question.

So now it seems the After Update function is hanging a bit as it runs the query (our SQL server is slloowwww). Is there a way to "preload" each query when the form loads and have the After Update function just select the preloaded query?

IE using the Form Load function run both the queries and save them to two separate constants and just use the After Update function to select the proper constant for my selection box?

[TG_W]

Not as far as I know. I have the same lag with my databases at work since I am using a front end and back end. Our network is slower than a car with a blown engine, so I feel your pain there.

[GoodOmens]

Not as far as I know. I have the same lag with my databases at work since I am using a front end and back end. Our network is slower than a car with a blown engine, so I feel your pain there.

Ah cool. I didn't know if access/vba could save a query result to a constant.

Anyways thanks again!