Assign number to new records if field is left blank or have user enter number

[Firefly]

i have 2 tables One called tblmembers

MemberID(PK)
Batchnumber
LastName
FirstName
Address1
Address2
City
State
Zip

The other called tblBatch

BatchID(Auto)
Batchnumber(PK)

As you can see by the sample database what i want to do is have a user enter a batch number for new records he/she has enterd. I cant figure out how to make this work. Basically what i want to do is when user click on command button add batch # to new records it opens a form called frmbatch.
user enters the number and it will be put in the table called members where is says batchnumber.

Any ideas.


thanks in advance

 

[liddlem]

Hi Firefly
I am left wondering what the connection is between a Batch and a Member
If I understand your problem correctly, you will want to connect many members to a batch.
What happens once a connection has been made?
Can they be connected again at a later stage and do you want to keep track of these connections or will you simply change the batch number in the Member table. (No audit trail)

If no audit trail is required, I would make the batch number the Primary Key and only field in the table. (unless you want to know what date the batch number was allocated?)

Then on the member form, display the BatchNo field from TblMembers.

And now there are 2 ways that I might handle this.
1. Add a (batchNo) dropdown box on the form that sources its data from the BatchNo table so that the user can select whatever batch they would like.
OR
2. The batch number gets allocated to the latest number as follows.
Have a (update batch) button next to the TblMembers/BatchNo field. When clicked, the BatchNo in the Member table is updated to the current (latest) batch number of the TblBatch. (You can do this via a Query - using "Top")

If you DO want an audit trail, then you will need a bridging table.
TblBridge has the following fields
BatchNo
MemberNo
LinkDate (optional)

Hope this gets you started

 

[Firefly]

There is no audit trail this is just a sample of a very complex database for personal use. I will try your way but maybe this clearer explanation might help what i'm trying to accomplish. I just designed 2 basic tables might not need the tblbatch

Example situation

Employees are given batches which contain maybe 100 to 200 new records to be added which contian bascic member info. Before employees get those batches they are scanned into a pdf file and that pdf file is assigned a batch number. The batch number acts as a link to where the original documents are stored on the server and if we need to pull the orginal info all the employee has to do is click the link or enter the batch number on server to prevew all files that where entered in that specific batch.

I do not want to have the employee type the batch number in manually for all the records he or she enters but will do it automatically.

Heres a sample database i found but this is for records that have not been printed yet and cant figure out how to modify it so i could enter my own number. see attached.

 

[Pat Hartman]

Your relationship is set up backwards. It seems that a batch can be entered by one person but a person may enter many batches. That means that the MemberID needs to go into the batch table indicating which person entered the batch. If you put the batch number into the Member table, how would you handle multiple batches for the same person?

MemberID(PK)
LastName
FirstName
Address1
Address2
City
State
Zip

The other called tblBatch

BatchID(Auto, PK)
Batchnumber(unique index)
MemberID (FK)
BatchDate
....

If you use an autonumber in at table, it must be the primary key. Jet has a problem if it is not. In the case of the batch table, you don't really need an autonumber since you already have a unique identifier. I prefer to use autonumbers as my primary keys so my choice would be as I posted. The autonumber would be the primary key and I would define a unique index on the batch number.