Selecting existing records for update in forms

[Christmas Chris]

ref. Access 2000, (optionally 2003)

Hi folks,

I'm quite a novice so hopefully this will be an easy one for you.

I've got a single table for inventory items which is updated twice by two different job functions/roles using two separate forms.

The first entry creates the record and inputs basic data including the unique key field in a combo box which is an "IDnumber". This
information is also physically placed on the items as a label for all future reference.

At a later time, the second form adds further data obtained from a second stage of inspection. At this point I want the user to be able to
access a look up of the IDnumbers requiring this additional data to be added and to simply select the appropriate record. The second
form displays most of the data already input via the first form, in protected fields, and then allows input of the further data from the
second stage inspection.

I have based the second form on a simple query which minimises the number of records in the look up based on the recorded time of the
first form input and the "status" of the record, and this part is working fine. However I am trying to keep the look up process simple for
inexperienced users and this is proving more difficult than I was expecting.

I can obviously scroll through the records selected by the query using the forward and back arrows at the bottom of the form but this
can still be a very "long winded" to find the desired record. Alternatively I am also aware I can have the query generate an entry box so
that only the record desired is selected, but the entry box generated only seems to permit a typed entry and does not seem to be able to
utilise a drop down selection.

The best alternative so far is, whilst the cursor is in the ID number field (combo box), to click on the "Filter by Form" tool on the toolbar,
obtain a drop down and select the desired IDnumber, and then click on the "Apply Filter" tool to give me a full display of the record.

This overall process works generally in the way I want but is complicated for the user as it takes them away from the form itself and
makes them use bits of Access that they and I would view as "techie" stuff. As I expected the user gets confused, tends to make the
steps in the wrong order, and potentially could even end up changing the key on an existing record without even realising it!

What I really want, as soon as the form starts up, is to have the user be able click the drop down on combo box on the form, select the
record and then have the full record display/refresh as though they'd applied a filter as above. At the moment, although the drop down
displays and allows the IDnumber to be selected, the rest of the record is not accessed and is not substitued for whatever was
displayed at form start up, (which gives rise to the risk described above).

One or two people to whom I have mentioned this, have suggested that I need to use a "macro" or "sub-routine" in VB to achieve this
effect. Whilst I am comfortable with RDBMS and have reasonable SQL experience, I am not a programmer, and I tend to view this as
delving a step too far into the techie side of things. I don't feel as though I'm trying to anything very complicated and I'm suprised if
there's no way within "GUI" Access or at least SQL to achieve this. Nevertheless I'll be interested in any solution that achieves the end
result which is not too complcated to set up and support!

Sorry this is a bit verbose, but I'm trying to be as unambiguous as possible. If anyone can help with some advice, I will be most
grateful.

Chris

 

[CyberLynx]

Within the second Form's Code Module under the OnClick event for the IDnumber Combo Box, you can use code like this to filter in the Reocrd(s) you want for further data entry:

   Me.Filter = "[IDnumberFieldInTable] = " & Me.IDnumber.Value
   'If the IDnumber is String (contains non-numerical text) the
   'you woul use:
   'Me.Filter = "[IDnumberFieldInTable] = [B][COLOR="Red"]'[/COLOR][/B]" & Me.IDnumber.Value & "[COLOR="Red"][B]'[/B][/COLOR]"
   Me.FilerOn = True

.