Command button to produce form based on drop down choice?

[yeahyeahyeah]

Per the instructions detailed here...
http://www.fontstuff.com/access/acctut08.htm

...I created a parameter query in the form of a drop down box that, once an
option is selected, should display a form with a number of fields pre-filled
from a record chosen by the drop-down selection.

The drop down has a command button with the following code in the Onclick
event:

Private Sub cmdCreateReport_Click()
DoCmd.OpenQuery "qryUIRFollowUp", acViewNormal, acEdit
DoCmd.Close acForm, "frmOpenUIRLookUp"

As it now stands when I click the button I get the form but none of the
selected data is filled in. The fields are blank.

What code to I need to add to the above to make the form hold the data
selected from the drop down?

Thanks,
David

 

[Smart]

I would use the value in the combo box and pass it to the query'qryuirfollowup'
base the form on that query and when the form opens it should be populated (as longs as the fields have their control source based on the queries fields

 

[yeahyeahyeah]

Thanks for the reply.

Ok, that sounds logical however do you have any idea what code I could write in order to make that happen? I am pretty oblivious when it comes to actually writing the code. If you could provide the code or point me to where I could find it I'd be greatly appreciative.
Thanks.

David

 

[ansentry]

Have a look at the attached sample.

Open the query (there is only one) and have a look at Post Codes you will see where the query gets its filter from.

 

[Smart]

If you look at the attachement I have amended the db by ansentry

so that it opens a form with the data onit rather rthan opening a query

 

[yeahyeahyeah]

Hi,

Thanks so much. However are you sure you attached the amended DB? The only one I see here is the original posted by Ansentry.

Let me know if it's here and I'm missing it.

Many thanks,
David

 

[Smart]

oops

I have attached it this time

The Form will need tidying up

 

[yeahyeahyeah]

Boy, I'm still not getting it...

Thanks so much for your help. I tried as best as possible to model the example you attached but I still cannot seem to get it to work and I'm at my wits end.

The only thing I can think to do is upload the database and see if there's anyone willing to download it and take a look. So I've attached a link here...

http://www.box.net/public/static/flkxlzav6f.rar

...where those willing to help are welcome to download it in .rar format.

The form that with the initial drop down box is called frmOpenUIRLookUp (UIR = Unusual Incident Report, by the way). The form that the drop down is supposed to feed into is frmOpenUIRLookUp and the query that is supposed to support that form is qryUIRFollowUpData.

Many, many thanks for the help,
David

 

[ansentry]

I have just downloaded your database, what you forgot to do is "Compact and Repair" it. It is now down to 2.93 MB

Will have a look later.

 

[yeahyeahyeah]

oops!

Thanks John. I'll do that right now and repost.

David

**********************

Here's the repaired and compacted version. Much smaller!!
http://www.box.net/public/static/flkxlzav6f.rar

 

[ansentry]

You have table1 as the source for the combo box (cboPendingUIRLookUp) and that look up the data fine.

BUT

The query that is the data source for the form you are trying to open (frmUIRFollowUp) does not have any data to display.

Open the query qryUIRFollowUpData take out the [forms]![frmOpenUIRLookUp].[cboPendingUIRLookUp] now open the query and you will see that there is nothing there.

You need to read about table Normalization, search the forum or use Google.

In my opion (and I could be wrong) you should do some reading then plan you database before starting again.

Sorry that I am the bearer of such bad news.

 

[yeahyeahyeah]

Hi John,

Well, I've thought for quite some time that I'd have to go back to the beginning and start over again, however, in the meantime is there a way to fix my current issue without restructuring the entire database?

Thanks,
David

 

[ansentry]

I would say No, however someone else may say Yes.