Adding Data to a Refference Table

[dbmanalser84]

Hi I have a refference table in my database which is connected to two tables. These tables are:

tblCars
#intCarID
txtModelName

tblSaleLine - Refference Table
#intSaleID
#intCarID
intQuantity

tblSales
#intSaleID
dteSaleDate

Now, I have forms for entering data in tblCars and tblSales and I want to build the form for tblSaleLine but I want to build it that way that all necesary data (car Models) for sale No.1 will be entered on the first record.

For example:
Sale ID----------CarID
1-------------1,2,3,4,5....
Something like selection meny. I want to se correspondin sale no. and all available cars I could add to that sale number, but the records should be added as follows to the tblSaleLine:

SaleID----------CarID
1---------------1
1---------------2
1---------------3

If someone didn't understand this right ask question and I'll try to clarify further. Thnx in advance.

 

[CyberLynx]

Now, I'm not sure if you're giving use all the Field names that will be on the new Form you intend to create. I suspect not but in any case perhaps your Form that you do create for the Table tblSaleLine should be Unbound to that Table.

The SaleID Form field can be auto populated by some means (there are several means available depending upon your true situation). The Series of CarID's you enter into the CarID Form Field (if any) will need to be Parsed and then individually inserted into the tblSaleLine Table by way of a INSERT INTO query when the SAVE button is selected. Of Course each CarID Parsed will be related to the SaleID supplied and entered into Table accordingly with both pieces of Data. You might have something like thins in the OnClick event for your Save button:

Dim CarIDArray() As String
Dim StrgSQL As String
Dim SaleNum as Long
Dim I As Integer

SaleNum = CLng(Me.[COLOR="Red"][I]FormSaleIDTextBoxField[/I][/COLOR])
CarIDArray = Split(Me.[COLOR="Red"][I]FormCarIDTextBoxField[/I][/COLOR], ",")

For I = 0 To Ubound(CarIDArray)
   StrgSQL = "INSERT INTO tblSaleLine (SaleID, CarID) VALUES (" & _
              SaleNum & "," & CLng(CarIDArray(I)) & ");"
   CurrentDB.Execute StrgSQL, dbFailOnError
Next I

Something like that. Keep in mind....the code above is not tested and there is neither Error Trapping nor are there any Conditional checks done (if either Form Field is Null). Items shown in Red Italic would need to contain the names you are using on your specific Form.

.

 

[boblarson]

And for heaven's sake remove any special characters (and spaces) from your field names. You should really only use underscores, if you need any special characters for field and object names. Otherwise keep away from them or it will eventually make your life a living hell. For example the # sign is a date delimiter so if you use that in your field name it can play havoc when you are trying to do something as Access will sometimes think that you are trying to provide it a date, when you are not.

See here for more about avoidint special characters.
http://support.microsoft.com/?id=826763

Also just an FYI - remember to avoid reserved words too:
http://support.microsoft.com/kb/q286335/

 

[dbmanalser84]

Oh, no...

And for heaven's sake remove any special characters (and spaces) from your field names. You should really only use underscores, if you need any special characters for field and object names. Otherwise keep away from them or it will eventually make your life a living hell. For example the # sign is a date delimiter so if you use that in your field name it can play havoc when you are trying to do something as Access will sometimes think that you are trying to provide it a date, when you are not.

See here for more about avoidint special characters.
http://support.microsoft.com/?id=826763

Also just an FYI - remember to avoid reserved words too:
http://support.microsoft.com/kb/q286335/

Oh, sorry, , the # sign represents the primary key field (I mark it that way when I create database on paper), I forgot to specify that. Thanx for advice anyway.