adding a new record at subform should update an existing record (not create new record)

[Mark Kubicki]

I have 2 tables that are related:
- tblManufacturer contains,among other things, a list of manufacturers
[strMfr] and the name of the agency that reps them [strRepAgency]; there
is only 1 of each manufacturer, and each manufacturer has only 1 rep
- tblRepAgencies contains detailed information about the RepAgency; each
RepAgency can represent many manufacturers

I have 2 forms:
- frmManufacturers is a continuous form with a text box: txtMfrm and a
combo: cboRepAgency (with a row source of tblRepAgencies); Manufactures'
names [strMfr] are entered in this form, and a rep agency [strRepAgency] can
be chosen for manufacturer
- frmRepAgencies is a single form where the RepAgencies are entered; it also
contains a continuous subform [subRepAgencies_MfrList] where manufacturers
that the agency represents can be entered / viewed

The problem I have is:
- When I add a new manufacturer to the frmRepAgencies subform
[subRepAgencies_MfrList], it creates a new record in tblManufacturer. This
causes a manufacturer to have multiple entries
- What I want it to do is update the manufacture's rep info on the existing
record (not create a duplicate entry for the same manufacturer.)

Any suggestions would be greatly appreciated...
thanks in advance,
mark

 

[strive4peace]

Hi Mark,

how about making AllowAdditions to subRepAgencies_MfrList = false?

use a listbox on the main form to show manufacturers that are not
currently using the rep shown in the main form

when a user selects a manufacturer from the listbox, use an update query
to set the rep, then requery the subRepAgencies_MfrList subform

on the AfterUpdate event of the listbox:
'~~~~~~~~~~~~~~~~~~~~~~~~~~
if isNull(me.listbox_controlname) then exit sub

dim strSQL as string

'change rep in manufacturer record
strSQL = " UPDATE tblManufacturer " _
& " SET strRepAgency = '" & me.RepAgency_controlname & "'" _
& " WHERE strMfr = '" & me.listbox_controlname & "'"
currentdb.execute strSQL

'clear listbox
me.listbox_controlname = null

'show manufacturer on the subform
me.subform_controlname.requery
'~~~~~~~~~~~~~~~~~~~~~~~~~~

you might want to first check to see what rep is listed for that
manufacturer -- and ask the user if they are sure they want to replace
it. If no rep is listed, don't even bother asking... and give a way to
delete a rep from the manufacturer record as well.

Warm Regards,
Crystal

remote programming and training

Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace

*
have an awesome day
*