Record navigation bar on entry form treats each value in the multivalued field as a separate record.

[LVB]

Hi Guys,
Newbie here.
I'm writing a small farm database to log cattle movements in and out the farm. I have several simple "lookup" tables (clients, locations, activities, vehicles, livestock. etc) which are used to simplify data entry into the main data table called the "Register".
The “Register” table is quite straight forward except it has 1 multi-value field called "livestock" which is where I store the cattle tag numbers of each of the cattle the shipment.
The main entry form "Register Entry" uses the lookup tables to enter in each movement and also allows me to move backwards and forward through the movements so I can review them at a glance.
Entering data into the form is fine, the problem arises when I use the entry form to browse through the data.
For example (assume an empty “Register” table): -

• Record navigation bar reads record 1 of 1
• I get a delivery of 5 cattle, I open the entry form and log the client, date time, and the tag numbers of the cattle (e.g. 123,112,333,432,534 these tag numbers are in the multivalued field). Checking the Register table, the entries appear on one row (the first row) so all seems as expected.
• If I exit the entry form and go back in, the record navigation bar for some reason reads record 1 of 5 (not 1 of 1) and if I create another entry, the record number, according to the Record navigation bar, is record 6 of 6. Instead of 2 of 2.
• For some reason the record navigation bar on the entry form treats each value in the multivalued livestock field as a separate record?
• However, when I check the “Register" table for this new entry, the new record is in fact on row 2.
• If the new entry has 20 cattle tags, the record navigation bar jumps to 25 records etc (but only shows as a single record in the table)

I’m not sure why this is happening but it means the number of records I will need to browse through will become very large as each movement could have up to 30 cattle, so as far as the entry form is concerned, I will have to step through 30 records before the form moves to the next “actual” record.
Any advice appreciated.

[June7]

What is the RecordSource of Register form - is it bound to table or query?

If you want to provide db for analysis, follow instructions at bottom of my post.

[LVB]

File attached.

Thanks for looking at it. It's doin my newbie head in :-)

[June7]

As I suspected, it is because of query used as RecordSource. Change RecordSource to table instead.

Combobox Activity is bound to wrong field.

Now to display related lookup info, include fields in combobox RowSource then use expression in textboxes referencing those combobox columns, like:

Activity combobox RowSource:
SELECT Activities.ID, Activities.Activity, Activities.Description
FROM Activities
ORDER BY Activities.[Activity];

Description textbox expression:
=[Activity].Column(2)

[mike60smart]

Hi
I would recommend that you do not use Multi Value Fields at all and use the normal Main Form / Subform setup.

See the modified Db attached

[Micron]

Basing the form on a table might be ok for a while - until you get enough records in it to slow things down. The problem with your query is not the mvf counting each element as a record, it's the inclusion of fields in the query whose records are broken out separately (Register.[Live Stock].Value, [Live Stock].[Tag Number]). If you remove those, you'll get one record in the query.

[LVB]

Thanks all for your responses. I took Microns advice and it now works as I was hoping it would.
June7 and Mike60smart, thanks for your responses and interest as well. Fantastic having such amazing resources!!

Thanks all.

L.

[ssanfu]

I modified the dB posted by Mike60smart. (just a little ) . This is how I would create it.

Feel free to ignore this version....