Startform to open specific record

  • Thread starter Thread starter Maarten
  • Start date Start date
M

Maarten

I've got a table (tbl_Offer) which is filled using a form
(frm_Intake). What I was thinking now is how to help users open
specific records and make new records. My idea is to use a start form
(frm_Start). The form which does nothing else then either open the
main form with a specific record displayed (users picked the one from
a combo box) or open the main form with an empty record set to make a
new intake.

The table (tbl_Offer) to which the main form (frm_Intake) is attached
has, besides the primary key, a unique number (str_NrIntake).
What I did is:
Make form frm_Start. Use the drop down wizard: "I want the combo box
to look up the values in a table or query). I selected the box to be
linked to tbl_Offer. From tbl_Offer I selected str_NrIntake, str_Name
and str_Project to be displayed to help the user select the correct
form. I sorted on str_NrIntake, decending. I hide the key. Then i said
"Remember this value for later use" creating an unbound box.
The I made a command button, using the wizzard again, Form Operations -
open form. Select frm_Intake and said: "Open form and find specific
Click to expand...
data to display ". I matched ComboBoxName with str_NrIntake.

When I execute this, a form is opened with apparently only a single
record (in navigation below only 1 nr), but empty.
Can anyone tell me what I'm doing wrong?

Thanks,
Maarten
 
Maarten

You may be doing more work that you need to.

You could add an unbound combobox to your frm_Intake and use it to select a
record to edit/view. Change the query that "feeds" your form (use a query
rather than basing the form directly on the table) to look at the combobox
for the ID value of the record it loads.

In the AfterUpdate event of the combobox, requery the form with something
like:

Me.Requery

With this approach, the form starts out empty (because there's no record in
the combobox, so the form's query returns the record with that ID -- i.e.,
none). Select a record from the combobox, and the form requeries, loading
the selected record.

No need for a startup form!

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Thanks Jeff,

That does the job indeed. Always good to think how to make life
easier.
Still I would like to give the users the idea that they can't switch
among records as they like. That they need to think and do a little
step extra. Point to a button to close a form. Choose to open an
intake (and think which one they need), or to make a new one.

If that takes more effort, then so be it.

Are there suggestions how to do that?

Maarten
 
Take a look at Access HELP on using OpenArgs. You can "pass" a value into a
form when you open it.

Or, as another approach, you could have the users click the button on Form1,
open Form2 (based on a query pointing at Form1 for its selection criterion),
then hide Form1. The use of the value in a query for feeding the form is a
similar notion to my earlier response.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
Hi Jeff,

You second option is exactly what I was trying to, at least: I think.
See my first message.

I made an open form button on form1 and linked it to the combobox in
form1. This combobox source is a field in a table. Form2 is based on
this table.
But I can't get it to work. Form2 opens nicely, but empty.

Regards,
Maarten
 
Hi Jeff,

I found the problem.
Apparently during the process where you open the form with a specific
record (the "Open form and find specific
data to display.") Access is using the primary key to allocate the
correct record. So even if you lookup the unique number "str_NrIntake"
in the combo box, it will still return the ID. If you then compare the
combobox value to the "str_NrIntake" in the tbl_Offer, you will never
get a match. Played around with this a little and I got it to work.

Thanks for your help! Getting to understand access a bit more.

Maarten
 
Back
Top