The form has a listbox (lst_cables) thats run from a query. This query
picks up data from a table (tbl_process) and displays; ProcessID,
ProcessDate and ProcessQuantity in the listbox.
The record source for the form is a query that picks up data from a
table (tbl_transaction) and picks up the fields; TransactionID,
TransactionNumber and TransactionStatDate.
The tables tbl_transaction and tbl_process have a relationship of one
- many respectively.
The purpose of creating a field called TransactionNumber is because
TransactionID is an autonumber and it kept jumping 2 records i.e.
after showing record 1 when going to a new record it would show 3
instead of 2, hence I created a field TransactionNumber that would
increment the number seqentially.
The problem I'm having is when I create a new entry in the listbox the
navigation bar of the form shows record 1 of 2 and record 1 holds the
correct TransactionNumber and TransactionStartDate but record 2 holds
the correct TransactionID. If I continue to enter the new data in
record 1 it will overwrite the previous data I had for that
TransactionID. However if I enter the new data in record 2 then when I
look back at the original table tbl_transaction then the
TransactionNumber will appear twice for different TransactionIDs hence
half the data appears in one TransactionID and the other half in
another.
I apologies for the long message but I wanted to try and give you a
clear understanding of the problem I'm encountering.
Thanks
I think you should be using a SUBFORM rather than a listbox. Listboxes aren't
designed to *add* data to their rowsource tables; they're designed to *select*
one or more records from their rowsource, for use elsewhere.
I suspect that *YOU* are the one creating this duplicate record in some sort
of code associated with the listbox ("create a new entry in the listbox").
You're not "creating a new entry in the listbox" - a listbox is not a data
repository! You're creating a new record in your Table, and *displaying* that
record in the listbox.
If this isn't clear to you, then neither is your process to me - it would help
if you would post the actual SQL string of the Recordsources and the VBA code
you're using.
John W. Vinson [MVP]
