Very basic form/sub-form question

[Marc T]

Hi,

I have two tables, one is a linked table from an external database, and the
other is a selection of field for manual entry (to be read alongside the
linked table).
e.g. Linked table has drawing number, revision, title & manual table has
transmittal number and comments.

What I'm trying to do is have a form that gives the detail from the linked
table above, and has a subform for entry of info into the manual table.

Ideally I'd like the main form body to have some way of selecting a record
(combo box?) rather than going through records using the arrows at the bottom
of the form as there can be several hundred possibly.

I've tried this a few ways, but can only seem to get it to work if I have
document number and revision as combo boxes with their own queries attached
(makes it very slow).

Any advice would be much appreciated!
Marc

 

[Jeff Boyce]

How depends on what, and you've not provided much description on your
underlying table/data structure. For example, I can't puzzle out how the
two tables are related ... what common data do they share?

Yes, you can use a combobox (unbound) in the header of your form, listing
possible records (just enough info to allow the user to pick the right one).
Use a query to 'feed' the form, and in the query, point to the combobox for
the recordID.

In the combobox's AfterUpdate event, use something like:

Me.Requery

The way this setup works is:
* The form opens, uses its query, which finds nothing in the combobox, so
loads "that" record (i.e., nothing).
* The user selects a record from the combobox.
* The combobox's AfterUpdate event fires, telling the form to requery,
causing the query to look (again) at the combobox, which now has a value.
* The form loads the record selected.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Marc T]

Something else has dawned on me...

Within the manual entry table I need to restrict the data to unique entries.
Not quite as straightforward as I would hope though...

The unique value within the linked table is a combination of three
fields.... and I need the manual entry table to be restricted to one entry
per combination of these three fields.

Any ideas on this as well?
Thanks!

 

[Marc T]

hi Jeff,

thanks for the reply!

The linked table contains information on documents in our ERP system (doc
number, revision, sheet, title etc). The manual input table I have set up in
Access also has doc number, revision and sheet along with a series of
additional fields for the user to add further information.

The number of records returned within the linked table can actually be in
the thousands, which was causing my problem before.
From what you've said below I'm gathering that setting a combo box that way
will only update the form once a value is selected correct? I'll give it a go
and see how I get on.

Many thanks!