Help! Static tables keep getting records added to them

[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!

 

[John Vinson]

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?

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]

 

[Guest]

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!

 

[jacksonmacd]

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!

**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[John Vinson]

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.

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]