Using a form to display previous entries and add new ones (1 Viewer)

J

jj44

New member
Local time
Today, 08:06
Joined
Oct 24, 2006
Messages
6
Hi all,

I'm trying to design a simple wildlife tracking DB, so that when myself or my colleagues spot an animal we can enter its ear tag number via a form. That will either bring us up a list of previous dates/locations that animal was observed, or if it's not in the DB, allow us to enter the new animal and location.

I have two main data tables:
dt_Animal- which stores the ear tag info, species, and other information about that unique animal
dt_Sightings - stores info on when/where animals been seen (linked to dt_Animal in a one-to-many relationship, of course)

What I want is that when you open up the form, you get a box (text or combo) to enter the animal's ear tag number.

- If it already exists in the DB, it brings up all the info on that animal and its previous sightings - with the option to fill out any missing info and (of course) to add a new sighting - and it WILL NOT add a new record to dt_Animal - just to dt_Sightings
- If it DOESN'T exist in the DB, it creates a new record in dt_Animal and allows you to enter any/all of the other information

Where I'm running into problems is that I don't want duplicate records in dt_Animal - each animal is unique. I haven't found a way to set up a form so that you don't get pop-up errors if you're typing in an ear tag number that's already in the system.

I've tried various tactics involving simple coding (NotInList, etc.) that I pulled out of this forum and other places, but haven't had any luck.

Thanks for your help!
 
Solo712

Solo712

Registered User.
Local time
Today, 10:06
Joined
Oct 19, 2012
Messages
828
jj44 said:
Hi all,

I'm trying to design a simple wildlife tracking DB, so that when myself or my colleagues spot an animal we can enter its ear tag number via a form. That will either bring us up a list of previous dates/locations that animal was observed, or if it's not in the DB, allow us to enter the new animal and location.

I have two main data tables:
dt_Animal- which stores the ear tag info, species, and other information about that unique animal
dt_Sightings - stores info on when/where animals been seen (linked to dt_Animal in a one-to-many relationship, of course)

What I want is that when you open up the form, you get a box (text or combo) to enter the animal's ear tag number.

- If it already exists in the DB, it brings up all the info on that animal and its previous sightings - with the option to fill out any missing info and (of course) to add a new sighting - and it WILL NOT add a new record to dt_Animal - just to dt_Sightings
- If it DOESN'T exist in the DB, it creates a new record in dt_Animal and allows you to enter any/all of the other information

Where I'm running into problems is that I don't want duplicate records in dt_Animal - each animal is unique. I haven't found a way to set up a form so that you don't get pop-up errors if you're typing in an ear tag number that's already in the system.

I've tried various tactics involving simple coding (NotInList, etc.) that I pulled out of this forum and other places, but haven't had any luck.

Thanks for your help!
Click to expand...

The easiest way to do this probably would be to check the entry in ear-tag field by a DLookup or DCount function.

Code: 
DCount("*", "dt_Animal", "eartagfield = " & Me!eartagfield)
Substitute the actual name for the "eartagfield". Also, if the ear tag is a text type field you need to write the test as

Code: 
DCount("*", "dt_Animal", "eartagfield = '" & Me!eartagfield & "'")


If the expression evaluates to enything but 0 the vaue entered is a duplicate. The ear-tag number or description is already in the system.
You then handle this the way you want. This routine should be in the BeforeUpdate event of the eartag field.

Best,
Jiri
 
J

jj44

New member
Local time
Today, 08:06
Joined
Oct 24, 2006
Messages
6
Thanks for the help. Based on that, with a bit of tweaking I've now got it so that when a duplicate value is entered, it lets the user know. That's great. I'm getting pretty close now. I have a subform set up to simultaneously enter sighting info.

Can you help me with the code for the following action - can't seem to figure it out:

If the eartagID value you've entered is already present in dt_Animal, instead of just popping up a warning, the form will bring up the associated data in the 'Sightings' subform, so you can see where the animal has been previously as well as add your new sighting.

I have gotten so far as that I can navigate through previous records, and the subform will bring up the associated sightings. But I can't find a way to link the two together so that when I type in a previously-entered eartagID, it will bring up those sightings.
 
Solo712

Solo712

Registered User.
Local time
Today, 10:06
Joined
Oct 19, 2012
Messages
828
jj44 said:
Thanks for the help. Based on that, with a bit of tweaking I've now got it so that when a duplicate value is entered, it lets the user know. That's great. I'm getting pretty close now. I have a subform set up to simultaneously enter sighting info.

Can you help me with the code for the following action - can't seem to figure it out:

If the eartagID value you've entered is already present in dt_Animal, instead of just popping up a warning, the form will bring up the associated data in the 'Sightings' subform, so you can see where the animal has been previously as well as add your new sighting.

I have gotten so far as that I can navigate through previous records, and the subform will bring up the associated sightings. But I can't find a way to link the two together so that when I type in a previously-entered eartagID, it will bring up those sightings.
Click to expand...

Hi jj44,
there is a bit of coding involved in this and I would need to know the setup which you are using. Are you using the After_Update event for the field as I suggested ? Also, do you want to let the user decide whether the entry (presumably to create a new entry in the dt_animal table) was in error or intentional action to retrieve the record without asking any questions ? Please, send the code for the sub/event in which the record selection takes place.

Best,
Jiri
 
J

jj44

New member
Local time
Today, 08:06
Joined
Oct 24, 2006
Messages
6
Hi Jiri,
I am using BeforeUpdate (which is what you had suggested)

I think in this case it would be OK for the DB to not ask questions (i.e. for it to always assume that entering a value is an intentional action and to assume that a user did not make an error).

The only code I have for the form right now is as follows:

Private Sub cboEartagID_BeforeUpdate(Cancel As Integer)
If DCount("*", "dt_Animals", "EartagID= '" & Me.EartagID & "'") > 0 Then
MsgBox Me.EartagID & " Already exists"
Cancel = True
Me!EartagID = ""

End If
End Sub

Thanks again!!
 
Last edited:
J

jj44

New member
Local time
Today, 08:06
Joined
Oct 24, 2006
Messages
6
Following Solo's banning, I took the problem to StackOverflow where it was solved...
I can't post a link but google this for the answer:
"Using a form to display previous entries and add new ones" site:stackoverflow.com
 
You must log in or register to reply here.

Similar threads

J
Estimate form with more than one entry
Replies
14
Views
577
mike60smart
mike60smart
H
Add a new form if combo shows error not in list
Replies
8
Views
760
CJ_London
C
M
VBA needed for add New Note in form
Replies
12
Views
214
MarcCastignani
M
E
Solved Txt box on Form to add data
2
Replies
26
Views
890
Emma35
E
N
NotInList playing up
Replies
7
Views
226
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom