Multiple Values for a Field

[mcgettge]

I am trying to create a form that will allow multiple values for one field. There are 10 fields that will be entered, but 9 of the remain the same for a multipe (but varying numbers) entries in a row. I am maintaining records in a table for retail deliveries. The driver, date, vehicle all remain the same for many items, but the package ID is different. I am trying to avoid retyping all of the information that remains constant for that route. Any suggestions?

 

[KenReay]

Hi

Difficult to be precise without knowing your table structure, but it sounds like a case for mainform/subform. The main form would hold your delivery info eg Date,Time Driver etc, the sub form the packageId. The linking field would be DeliveryId ?

Hope this helps

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mcgettge]

Right now the table structure is very simple, there is only one. Everything is keyed in, line by line. There is a lot of repetition in the data entry which is what I am trying to eliminate. Could you explain the subform to me? I am not very familiar with its uses or creation.

 

[mph1]

The driver, date, vehicle all remain the same for many items, but the package ID is different. I am trying to avoid retyping all of the information that remains constant for that route.

Since data doesn't change much from record to record use the default value of each filed with information that is most often used. You could also use a drop down for each field. If you do, enter code in the Not In List so you can add other information when necessary.

Another thing I would strongly suggest, especially if using the Default Value, it to add code in the On Click event of the button that adds the record, a vb/yesno and a message "Are you sure all fields are correct" or something like that. If while entering data you become distracted it will be easy to miss a fiel.


HTH

An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[mcgettge]

The problem with the default value is that it might change every 10 entries when there could be 1000 in a day. Instead of making the full 1000 entries, I would like to be able to enter 100 records with one of the fields being able to accept the 10 values for my package ID thus creating 1000 records without all of the needless repetition.

 

[KenReay]

Hi

I was assuming at least two tables were present ie a Deliveries table:

DelivId - PK
DriverId
TimeDelivery
DateDelivery
..etc

and a Package Table

DelivId )
PackageId ) PK
..etc

You would have a subform (Datasheet, or continuous) based on the Package TAble, on a main form which was based on the Deliverytable, the link field would be DeliveryId

so you would enter one lot of delivery info, and 'n' Package Ids (plus other package related data if any)



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[mph1]

Based on the volume of entries you indicate, I think your best bet is to use the drop down. Although you don't want the repetition of going thru every field for each record, some things will change at some point in time. It seems like the information you have can be critical and if one field is incorrect....... problem.

An investment in knowledge always pays the best dividends.

by Benjamin Franklin

 

[willir]

mcgettge

I understand your desire to create a simple database. but as suggested by Mr. Franklin, based on the number of entries, I would suggest that you normalize your data to avoid problems later on.

The use of combo boxes will really help speed data entry. Moreover, you can set defaults which would use your preferred driver, vechicle. The date can use the default Now() or int(Now()).

To use combo boxes that reflect back on the same table as you seem to be doing can be done.

Let's take the Driver.

I am going to assume the table is
tblDeliveries

DeliveryID - primary key, auotnumber
DriverID - numeric or text, text is more friendly, use initials
DeliveryDate - date / time, default int(now())
DeliveryTime - date / time
etc

For the combo box for the Driver...
Control source - DriverID
Rowsource - "select distinct DriverID from tblDelveries order by DriverID"
This will be different than what the combo box wizard will create. It would create "select distinctrow DeliveryID, DriverID ...". If you use the wizard, you will have to change the row source, and change the column width on the format tab from " 0";1"... " type of thing to 1". Likewise, change the column count to 1.

But it would be far better to have the combo box reference a dupporting table, say tblDrivers. Now you can provide the DriverID, name, and other contact info.

Richard

 

[mph1]

As you will note, there is usually more than one slolution to a db problem. What Richard mentioned about normalization is extremely important. Hopefully you appreciate it is sometimes very difficult to determine the level on knowledge of a poster. Sometimes too much information can become overwhelming. Your question was not a simple one ie, is my code correct, where maybe the only problem was a character in the wrong place.

As far as I am concerned, everyone started with zero knowledge and then built upon it. That's how we learn.


Good Luck and continued success.

An investment in knowledge always pays the best dividends.

by Benjamin Franklin