multiple table query adding records

[Jessica]

I have a ODBC to Oracle in Access to two tables. Table1
has vendorID, VendorName, and PIDM. Table2 has VendorName,
VendorID, PIDM and 10 other fields. I am creating a
multiple table query with those two tables on a form. As
soon as I attach the query to the form my addition rights
are not available. I want to be able to add records to
Table1 and only look up values from Table2 with combo box.
Any suggestions please!!

 

[GVaught]

When you try to use two tables to enter data into a form in Access you must
make sure that the two tables used are joined so that they are updateable;
this rarely happens. You should not use the two tables in the one query.

If table 2 will only be a lookup to add data to table1 via the form, then
don't add it to your first query. You can create a separate query for table2
if you want the data to be sorted a certain way. On your form you can use
combo boxes connected to table2 or the query you created to add the data to
the table1 field. To be sure that referential integrity stays with the data
you enter; be sure to add table2's primary key to the query (if used).

Example:
Table1
OrderID(PK)
OrderDate
EmployeeID (FK) (related to Employee table)
....other fields

Table2
EmployeeID(PK) Matches FK in Table1
EmployeeFName
EmployeeLName
EmployeeMI
..... other fields.

Query2 created from Table 2:
EmployeeID
EmployeeLName (Sort Ascending)
EmployeeFName (Sort Ascending)


Form:
EmployeeName: (combo box with source set to Lookup data from Query2)[ Combo
Box Properties: Bound column: 1; Column Count: 3; Column Head: No; Column
Widths: 0; 1"; 1" (zero means not to show EmployeeID inside combo box, which
leaves only the EmployeeLName, EmployeeFName showing); List width: Set Total
of Column Widths: 2"; List Rows: 8]
Rest of fields for form.....OrderID... OrderDate etc.

Hopefully, this has not confused you.