New Record

[Asif]

Whenever I enter new data into a form and then I run a query on it
half the data is displayed in one record and the other half in another
i.e. the autonumber changes

When I create a new record the navigation bar shows 1 of 2 and half
the data is in record 1 and the other half in record 2. As a result of
this autonumber changes which is incorrect for my purpose.

Any suggestions on what to do?

Thanks

 

[John W. Vinson]

Whenever I enter new data into a form and then I run a query on it
half the data is displayed in one record and the other half in another
i.e. the autonumber changes

When I create a new record the navigation bar shows 1 of 2 and half
the data is in record 1 and the other half in record 2. As a result of
this autonumber changes which is incorrect for my purpose.

Any suggestions on what to do?

Thanks

Correct the error in your form.

If you'ld like help doing so, please post some details - what's the form's
Recordsource? What's the query? How are you running the query? WHY are you
running a query (rather than just using a bound form)?

John W. Vinson [MVP]

 

[Asif]

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

 

[John W. Vinson]

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]