Create a record in another table if it does not exist as active

[Tami Lamz]

Hello Everyone,

I am hoping someone could help me figure out the logic to a problem I have.

I need to track waste barrels and their destruction dates. The same waste
barrel number can be used multiple times. Each time it is used, I want to
record the destruction date (and this date must be saved, it cannot be
overwritten). I generated an autonumber in the waste barrel table for each
record. My problem is that I want the user to be able to enter the waste
barrel number (not the unique ID) into a work order form (they will know the
barrel number when they are entering the work order but will not know if the
barrel exists in the waste barrel table or not). The barrel number would be
stored in the work order table. The same barrel number could be entered
into multiple work orders but would be related to only one active waste
barrel. How do I allow the user to enter the barrel number in the work
order form and create a record in the waste barrel table if there is not an
active waste barrel record (active meaning that it does not have a
destruction date recorded)? I am stumped....

Thanks in advance for your help!

Tami

 

[Steve Schapel]

Tami

One approach would be to put code in the Before Update event of the
Barrel Number control on the form, to check if there is a an active
record for the barrel number, and not allow the entry if there is. For
example...
If DCount("*","Work Order","[Destruction Date] Is Null And [Barrel
Number]=" & Me.Barrel_Number) > 0 Then
MsgBox "This Barrel Number is active!"
Cancel = True
End If

 

[Tami Lamz]

Hmm, I didn't do a great job explaining, but I think your suggestion may
have given me a good idea.

I want to allow them to enter the barrel number in the work order under any
circumstance (i.e. whether the barrel number is already active or does not
exist). I then want to create a record in the barrel table if there is not
an active barrel for that barrel number. I wonder if I could use the before
update event and create a barrel if there isn't an active barrel? Thinking
it through, maybe I shouldn't store the barrel number in the work order
table but instead store the unique autonumber assigned by access. Is there
a way to retrieve the autonumber for a waste barrel id only if it is active
and store that autonumber in the work order table? And at the same time,
assign an autoumber if there isn't an active waste barrel?

Thanks for your help!

Tami

One approach would be to put code in the Before Update event of the
Barrel Number control on the form, to check if there is a an active
record for the barrel number, and not allow the entry if there is. For
example...
If DCount("*","Work Order","[Destruction Date] Is Null And [Barrel
Number]=" & Me.Barrel_Number) > 0 Then
MsgBox "This Barrel Number is active!"
Cancel = True
End If

--
Steve Schapel, Microsoft Access MVP

Hello Everyone,

I am hoping someone could help me figure out the logic to a problem I have.

I need to track waste barrels and their destruction dates. The same waste
barrel number can be used multiple times. Each time it is used, I want to
record the destruction date (and this date must be saved, it cannot be
overwritten). I generated an autonumber in the waste barrel table for each
record. My problem is that I want the user to be able to enter the waste
barrel number (not the unique ID) into a work order form (they will know the
barrel number when they are entering the work order but will not know if the
barrel exists in the waste barrel table or not). The barrel number would be
stored in the work order table. The same barrel number could be entered
into multiple work orders but would be related to only one active waste
barrel. How do I allow the user to enter the barrel number in the work
order form and create a record in the waste barrel table if there is not an
active waste barrel record (active meaning that it does not have a
destruction date recorded)? I am stumped....

Thanks in advance for your help!

Tami

 

[Steve Schapel]

Tami,

Yes, I did misunderstand you. Sorry.

I see no reason not to use the Barrel Number in the Work Order. And
yes, you could create a barrel if there is not an active one. Assuming
I now have a better idea of what you are doing, something like this...

If IsNull(DLookup("[BarrelID]","Barrel","[Destruction Date] Is Null
And [Barrel Number]=" & Me.Barrel_Number)) Then
CurrentDb.Execute "INSERT INTO Barrel ( [Barrel Number] ) VALUES (
" & Me.Barrel_Number & " )"
End If

I would probably put this code on the After Update event of the Barrrel
Number control on the Work Order form, rather than Before Update. The
Append Query may need to be changed if there are other fields in the
Barrel table that also need data entered from the outset.

 

[Tami Lamz]

Thanks so much!!! That is exactly what I needed.

Tami

Tami,

Yes, I did misunderstand you. Sorry.

I see no reason not to use the Barrel Number in the Work Order. And
yes, you could create a barrel if there is not an active one. Assuming
I now have a better idea of what you are doing, something like this...

If IsNull(DLookup("[BarrelID]","Barrel","[Destruction Date] Is Null
And [Barrel Number]=" & Me.Barrel_Number)) Then
CurrentDb.Execute "INSERT INTO Barrel ( [Barrel Number] ) VALUES (
" & Me.Barrel_Number & " )"
End If

I would probably put this code on the After Update event of the Barrrel
Number control on the Work Order form, rather than Before Update. The
Append Query may need to be changed if there are other fields in the
Barrel table that also need data entered from the outset.

--
Steve Schapel, Microsoft Access MVP

Hmm, I didn't do a great job explaining, but I think your suggestion may
have given me a good idea.

I want to allow them to enter the barrel number in the work order under any
circumstance (i.e. whether the barrel number is already active or does not
exist). I then want to create a record in the barrel table if there is not
an active barrel for that barrel number. I wonder if I could use the before
update event and create a barrel if there isn't an active barrel? Thinking
it through, maybe I shouldn't store the barrel number in the work order
table but instead store the unique autonumber assigned by access. Is there
a way to retrieve the autonumber for a waste barrel id only if it is active
and store that autonumber in the work order table? And at the same time,
assign an autoumber if there isn't an active waste barrel?

Thanks for your help!