Parameter using a combo box 2

[CJSilver]

I have a form in a database format. The form is based on a query. I want the users to be able to select the parameter from a combo box, then have the form show the data based on the parameter selected. Here is what I have tried. I created a combo box in the form that is unbounded, it pulls from a table to get the list. In the afterupdate event I have the following code: (The credit for this code goes to Tranman)

Private sub Combo91_afterupdate()
On Error GoTo Err_Combo91_afterupdate

    Dim stDocName As String

    stDocName = "Team 1New"
    DoCmd.OpenQuery stDocName, acNormal, acEdit

Exit_Combo91_afterupdate:
    Exit Sub

Err_Combo91_afterupdate:
    MsgBox Err.Description
    Resume Exit_Combo91_afterupdate
End Sub

What happens is the form opens, then after selecting from the combo the correct parameter, the query opens showing the correct data. But the Form does not update and if I close the form and open it again, it is now blank, no data in it at all.

What I want to happen, is the user does not see the query opening (if possible) and the form updates after the query runs, or if the parameter is in a seperate form, that the correct form opens after the query runs.

I also tried doing this with a click button, but I could not get the button to show up when the form was in database view.

C. Johnson

http://www.SilveradoCable.com

 

[hkaing79]

is the query an action query, i.e. make table, update, or delete?

 

[PHV]

If the RecordSource of the form is "Team 1New", why not simply this ?
Private Sub Combo91_AfterUpdate()
Me.Requery
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[famehrie]

CJSilver,
If I understand you correctly what you are trying to do is base the rest of the forms data on the selection of a combo box. If I am correct the check out this thread. I posted something which may help you.

Kevin D.

 

[famehrie]

Forgot the thread
702-1021328

 

[CJSilver]

I thank all of you for replying.

One of the problems I seem to be having is that if I take the criteria out of the query, then I can open my form and see all the data. But of course I can't use any parameters. But when I put the criteria in; [forms]![team 1new]![Combo91] then my form opens blank, I can't even select anything from the combo box for the parameter. If I run the query the parameter window opens up, I can enter a parameter and it works fine, then I run the form and it is still blank.

Let me give some details on what I am doing. I hope this makes sense.

I am creating a scheduling tool for our manufacturing department. A tool for them to be able to assign jobs, plan dates etc. The parameter is so that each assembler can pull up the jobs they are assigned.

Most of my tables are SQL tables that come from our manufacturing software, so I can't change anything in them, they are not updatable tables.

I created a table that is updatable, I have a form that writes to this table, this is where jobs are assigned dates and who will build them.

I then link that table with my SQL tables in a query, I have a new form that is in datasheet format based on that query, this is just a report, it is not an updatable form. This is the form I want my parameters on, I want the assemblers to be able to open the form, select their name from a drop-down and have the form show just their jobs.

I know their are much better ways of doing this than the way I have done it, and I am working towards improving it, but I am not a coder at all and all the ways I know of to improve it take code.

PHV,

As I mentioned above, I am not a coder, so I am not sure of what you are suggesting. Do you want me to get rid of the code I have and replace it with yours or add yours to mine? I tried both, but since my form is opening blank I have no way to try it out.

The strange part (to me at least) is that when I first entered the code in my form I could open it, the data was still there, I could select my parameter, then the query would open with the correct data showing, by my form didn't refresh, when I closed the form and re-opened it, it was blank and has been blank ever since.

C. Johnson

http://www.SilveradoCable.com

 

[CJSilver]

Okay, I have it working, though it still is not quite what I am looking for.

I took the combo box out of the final form and created a new form that just has a click button and the combo box. I used the code in my first post on the click event.

So now, the click form is opened, the name is selected in the combo box, then the button clicked. The query opens, then the user needs to open a second form to see the finished results.

Is there a way to have the second form open automatically after the button is clicked? Also can the query close automatically after the form opens?



C. Johnson

http://www.SilveradoCable.com

 

[PHV]

Use DoCmd.OpenForm instead of DoCmd.OpenQuery

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CJSilver]

I am still having one problem with this. WHen I execute the click command the form opens now, but it is opening in Form view, not datasheet view. I have tried going into the properties for that form and setting no to all views except datasheet, but it still opens in form view.



C. Johnson

http://www.SilveradoCable.com

 

[CJSilver]

Does anyone know of a way to force the form to open in datasheet view after DoCmd.OpenForm is executed?

C. Johnson

http://www.SilveradoCable.com

 

[RoyVidar]

The second arguement of the openform method should give you the opportunity for that:

[tt]docmd.openform "yourform", acFormDS, ...[/tt]

Also, I think you can switch view on the fly, I think, by using something like this:

[tt]docmd.runcommand accmddatasheetview[/tt]

Roy-Vidar

 

[CJSilver]

Thank you RoyVidar. You have come through again!

C. Johnson

http://www.SilveradoCable.com