Data Entry Form problem. Please help!!

[edworboys]

I have designed a data entry form with a number of fields and a sub
form. The first field (Country) is a combo box and the user selects a
country. This, in turn reduces the number of options in the second
field (Prospect Name). The user then uses the subform to enter a
document location.
The problem is this:
When the user selects a prospect name, rather than selecting the
existing record it creates a new one with the same name. I have tried
disabling duplicates but then I just get an error message when adding
records.
Although I have somehow managed to create a relatively advanced
database I am a complete novice with access!
Please respond with any ideas or if you need more information.
THANKS
Ed

[Rick Brandt]

<ed*******@gmail.com> wrote in message
news:ch********@odbk17.prod.google.com...

I have designed a data entry form with a number of fields and a sub
form. The first field (Country) is a combo box and the user selects a
country. This, in turn reduces the number of options in the second
field (Prospect Name). The user then uses the subform to enter a
document location.
The problem is this:
When the user selects a prospect name, rather than selecting the
existing record it creates a new one with the same name. I have tried
disabling duplicates but then I just get an error message when adding
records.
Although I have somehow managed to create a relatively advanced
database I am a complete novice with access!

ComboBoxes are typically used to navigate or they are bound and used for
data entry. Sounds like you want the former, but have created the latter.

If you will sometimes be changing data in both the main form and the
subform then you need your ComboBoxes to be separate controls that are
unbound and are only used to navigate to the proper record in the main
form. Otherwise the main form opens at record one and anything you do with
the ComboBoxes is changing the data in that record.
--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Janross]

>> I have designed a data entry form with a number of fields and a sub

form. The first field (Country) is a combo box and the user selects a
country. This, in turn reduces the number of options in the second
field (Prospect Name). The user then uses the subform to enter a
document location.
The problem is this:
When the user selects a prospect name, rather than selecting the
existing record it creates a new one with the same name. I have tried
disabling duplicates but then I just get an error message when adding
records.
Although I have somehow managed to create a relatively advanced
database I am a complete novice with access!

ComboBoxes are typically used to navigate or they are bound and used for
data entry. Sounds like you want the former, but have created the latter.

If you will sometimes be changing data in both the main form and the
subform then you need your ComboBoxes to be separate controls that are
unbound and are only used to navigate to the proper record in the main
form. Otherwise the main form opens at record one and anything you do with
the ComboBoxes is changing the data in that record.

To expand on Rick's reply, in case you need more detail, consider this:

In the form header, put two unbound combo boxes (i.e., the rowsource is the
same as you've had it, but the control source is blank). Keep the code that
narrows the prospect list based on country selected. Then, in the afterUpdate
property of the prospect combo, put code that moves you to the appropropriate
record.

aircode (watch for line wrap); assume your combo with the prospect list is
called prospectcombo:
dim rs as dao.recordset
set rs=me.recordsetclone
dim strcriteria as string
strcriteria="prospectID=" & me!prospectcombo
rs.findfirst strcriteria
if not rs.nomatch then
me.bookmark=rs.bookmark
endif
rs.close
set rs=nothing

Hope that helps somewhat

Jan
Jan Stempel
Stempel Consulting