Using a Query in a Form

[CorporateQAinTX]

MERRY CHRISTMAS EVERYONE!

I just had to do that. Well, on to the problem. I've got a form for data
entry with the control source as the main table...I've also created a
duplicate of the same form with the control source as a query in order to
find data by the primary key and edit the record. I don't like this though.
Besides, after listening to all of you MVP's I'm really getting the picture
that duplicate objects is not a good idea. Thanks for that by the way. How
can I set up the form to enter records to the main table and pull records out
once I enter a value in the Primary Key control? This would also be a way to
insure that duplicate records are not being created, right? I hope I can hear
something soon. If not, I'll be back next week. Everyone have a safe and
Merry Christmas!

TIA,

Garrett

 

[Tom van Stiphout]

On Wed, 24 Dec 2008 06:42:05 -0800, CorporateQAinTX

Merry Christmas to you.
I understand you want to use the same form for data entry and for
looking up records by PK. Yes, that is possible. We do this all the
time. Just put the PK field on the form, set focus there, and hit
Ctrl+F to find another value. DO NOT try to overwrite the PK value by
entering the data you want to find in the PK field.

A PK, being a unique index, cannot have duplicates.

-Tom.
Microsoft Access MVP

 

[Mike Painter]

A combobox can be used to move to a record and the wizard will walk you
through this.
Usually this would use a description of the item rather than the key for the
item. Most people remember widget, green with sparkles better than
erer555er44.
In general a primary key does not have any value beyond it being the key
value.
If it is a phone number, SSN or ... and it must be used the combobox would
be modified to allow additions.
I don't have the code at hand to show this.

 

[CorporateQAinTX]

I don't quite understand how that would work if I can't enter the PK. The PK
is a numerical value taken from a paper form that someone would fill out. I
could possibly see do something like that if it was an autonumber, but they
have to be able to enter the number to search by, right? Maybe I'm just
totally misunderstanding.

What I had thought of was running a query that I wrote after the PK was
entered, but I don't understand how to set it up to run the query and
populate the form if there is a matching record, or if there isn't a matching
record would the data entered be added to the table as a new record?

I may be trying to make this more complicated than it should be. Too much
egg nog...

I'll try out your suggestion though. Thank you.

 

[Tom van Stiphout]

On Wed, 24 Dec 2008 08:49:01 -0800, CorporateQAinTX

It's OK to have a PK that you hand-enter. For example you could make
it a Number > Long Integer. Just don't try to use the SAME field for
both data entry and searching. It can be done, but it's an advanced
topic. Much better to stay with Ctrl+F for searching.

There is indeed a way to test if a value is already in the db before
saving the record (when a big error would occur): in the
<PK_field>_BeforeUpdate event write the one-liner:
Cancel = (DCount("myPkField", "myTable", "myPkField=" &
Me.myPkControlName) > 0)
(of course you substitute your names for the placeholders)
What this line does is count the number of records in your table with
the current PK value. If greater than 0 the Cancel argument is set to
True and the user cannot leave the field without providing a better
value. I'm sure you can come up with a handy messagebox to explain to
the user what just happened.

-Tom.
Microsoft Access MVP