Help! Static tables keep getting records added to them

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello -

I have tables that are essentially lookup tables that are displayed in the
form as combo boxes. For some reason, everytime a record is added, it adds a
record to my lookup tables (which should be static tables). Any idea what
may be causing this? I am working from a query instead of using the tables
directly. Is there something wrong with my query to cause records to be
added?

Any help will be greatly appreciated!
 
Hello -

I have tables that are essentially lookup tables that are displayed in the
form as combo boxes. For some reason, everytime a record is added, it adds a
record to my lookup tables (which should be static tables). Any idea what
may be causing this? I am working from a query instead of using the tables
directly. Is there something wrong with my query to cause records to be
added?
Click to expand...

Almost certainly; care to post the query so we might have a chance to
see what it is?

Open the query in SQL view and post it here.

As a rule, the query for a combo box should be based on the Lookup
table ONLY - it sounds like you may have joined the lookup table to
your form's recordsource table.

John W. Vinson[MVP]
 
Hi John -

Guess that was a stupid question -- of course something's wrong with my
query . . .

At any rate, here it is:
SELECT tblMain.CallID, tblMain.tblAgent_ID, tblAgent.[Agent],
tblAgent.CallDate, tblMain.CallerName, tblMain.tblDeclaration_ID,
tblDeclaration.Declaration, tblMain.tblCallType_ID, tblCallType.CallType,
tblMain.NumbApps, tblMain.tblLoanType_ID, tblLoanType.LoanType,
tblMain.Comment, tblMain.Resolved
FROM (((tblMain LEFT JOIN tblCallType ON tblMain.tblCallType_ID =
tblCallType.ID) LEFT JOIN tblLoanType ON tblMain.tblLoanType_ID =
tblLoanType.ID) LEFT JOIN tblAgent ON tblMain.tblAgent_ID = tblAgent.ID) LEFT
JOIN tblDeclaration ON tblMain.tblDeclaration_ID = tblDeclaration.ID;

I have four tables: tblMain, tblAgent, tblDeclaration, tblCallType,
tblLoanType.

I guess I don't understand lookup tables. If I was doing this in Sql
Server, I would create an associate table for each of the relations.

Any help will be forever and eternally appreciated!
 
PMFJI

It looks to me like your form should be based solely on tblMain,
foregoing all the joins that you have created. I would start by
redesigning the form to show only the foreign key to the various
tables in a textbox, eliminating the "readable" display of the various
lookup items.

Once that was done and tested, I would replace the textbox for each of
the lookup items on the form (Agent, Declaration, CallType, LoanType)
with a combo box. Each combo box will have an independent RowSource
that will display the human-readable value from the lookup table
instead of displaying the foreign key that is stored in the tblMain.

I see that you have included tblAgent.CallDate in your query. That
implies that each agent has exactly one call date. Is that correct? Or
do you need a separate table, which would allow you to store multiple
call dates per agent? The answer depends on the meaning of "call
date", which only you can answer.

HTH



Hi John -

Guess that was a stupid question -- of course something's wrong with my
query . . .

At any rate, here it is:
SELECT tblMain.CallID, tblMain.tblAgent_ID, tblAgent.[Agent],
tblAgent.CallDate, tblMain.CallerName, tblMain.tblDeclaration_ID,
tblDeclaration.Declaration, tblMain.tblCallType_ID, tblCallType.CallType,
tblMain.NumbApps, tblMain.tblLoanType_ID, tblLoanType.LoanType,
tblMain.Comment, tblMain.Resolved
FROM (((tblMain LEFT JOIN tblCallType ON tblMain.tblCallType_ID =
tblCallType.ID) LEFT JOIN tblLoanType ON tblMain.tblLoanType_ID =
tblLoanType.ID) LEFT JOIN tblAgent ON tblMain.tblAgent_ID = tblAgent.ID) LEFT
JOIN tblDeclaration ON tblMain.tblDeclaration_ID = tblDeclaration.ID;

I have four tables: tblMain, tblAgent, tblDeclaration, tblCallType,
tblLoanType.

I guess I don't understand lookup tables. If I was doing this in Sql
Server, I would create an associate table for each of the relations.

Any help will be forever and eternally appreciated!
Click to expand...

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
 
I guess I don't understand lookup tables. If I was doing this in Sql
Server, I would create an associate table for each of the relations.
Click to expand...

Sure, you have the table. But you don't base your Form on *every table
in your database!*

The main form should be based on the main table... and just on the
main table.

A Combo box is a tool which allows you to select a record from another
table (usually a "lookup table" so-called, though I don't recommend
using Microsoft's Lookup Field type). The combo box's Row Source
should be just the lookup table, of (more commonly) a query sorting
that table into a meaningful order. It allows you to store the combo's
Bound Column into a field in the form's recordsource, typically a
Foreign Key field.

For example, in your case, it would appear that you want to look up
(say) a particular agent and store that Agent's ID into the Loans
table. If so, you'ld simply have a form based on Loans; and a combo
based on a query like

SELECT AgentID, AgentLastName & ", " & AgentFirstName
FROM Agent
ORDER BY AgentLastName, AgentFirstName;

This combo's Control Source would be the AgentID field in Loans;
you'ld probably set its Column Count to 2 and ColumnWidths to 0;1.5 in
order to *display* the agent's name, while storing the AgentID.

John W. Vinson[MVP]
 
Back
Top