Update record if exists or add if it doesn't

[sword856]

Hello all out there.
I have tried to use this forum trying to solve this problem, and have
not found anything that fits what I need, at least not yet. I have a
form that records class history, containing the three fields of
FullName, ClassName, and DateTaken.

When a user enters values for FullName and ClassName, I want any
existing record with that combination to updated with the new date
provided by the user. This is because I am only interested in the
most recent time any class was taken by a given person.

It would be best if the user didn't edit the existing record but
rather replaced it with the new one, if that makes sense. This is
because I have the date box set up so that they can "lock" the
date(with a button) so that the datee they enter moves to the new
record when they hit my "add new" button.

If this is possible, any help would be greatly appreciated.

TIA,
George

 

[Wayne-I-M]

Hi

It's my personal preference but I wouls not delete the record of classes
taken. I wuld store this in a seperate table linked the table contining
students details.

But as you asked (you know your database and I dont).

On your form I assume you have the folloing fields (change the names in the
cde to what they really are).
txtFullName, txtClassName, and txtDateTaken - I also assume that each record
have an ID field called txtID and that all this is stored in a table called
tblDetails

I assume you will fill in the txtFullName 1st and then txtClassName

In the BeforeUpdate event of txtClassName put this

Private Sub CDSurname_AfterUpdate()
If ((DLookup("[txtID]", "[tblDetails]", "[txtFullName] ='" &
Form!txtFullName & "' AND [txtClassName] = '" & Form!txtClassName & "'")))
Then
Beep
MsgBox "This person has already done this class", vbOKOnly, "C;ass
Checker"
Else
Me.txtDateTaken.setfocus
Me.txtDateTaken = Null
End If
End Sub


Of course you can refine this be putting vbyes vbno - cancel = true, etc,
etc, etc after the message, or more normally as part of it - so that an
action is taken to cancel the input or allow it (there may be 2 John Smiths
in the same class)

Good luck

 

[sword856]

Hi

It's my personal preference but I wouls not delete the record of classes
taken. I wuld store this in a seperate table linked the table contining
students details.

But as you asked (you know your database and I dont).

On your form I assume you have the folloing fields (change the names in the
cde to what they really are).
txtFullName, txtClassName, and txtDateTaken - I also assume that each record
have an ID field called txtID and that all this is stored in a table called
tblDetails

I assume you will fill in the txtFullName 1st and then txtClassName

In the BeforeUpdate event of txtClassName put this

Private Sub CDSurname_AfterUpdate()
If ((DLookup("[txtID]", "[tblDetails]", "[txtFullName] ='" &
Form!txtFullName & "' AND [txtClassName] = '" & Form!txtClassName & "'")))
Then
Beep
MsgBox "This person has already done this class", vbOKOnly, "C;ass
Checker"
Else
Me.txtDateTaken.setfocus
Me.txtDateTaken = Null
End If
End Sub

Of course you can refine this be putting vbyes vbno - cancel = true, etc,
etc, etc after the message, or more normally as part of it - so that an
action is taken to cancel the input or allow it (there may be 2 John Smiths
in the same class)

Good luck

--
Wayne
Manchester, England.

Hello all out there.
I have tried to use this forum trying to solve this problem, and have
not found anything that fits what I need, at least not yet. I have a
form that records class history, containing the three fields of
FullName, ClassName, and DateTaken.

When a user enters values for FullName and ClassName, I want any
existing record with that combination to updated with the new date
provided by the user. This is because I am only interested in the
most recent time any class was taken by a given person.

It would be best if the user didn't edit the existing record but
rather replaced it with the new one, if that makes sense. This is
because I have the date box set up so that they can "lock" the
date(with a button) so that the datee they enter moves to the new
record when they hit my "add new" button.

If this is possible, any help would be greatly appreciated.

TIA,
George- Hide quoted text -

- Show quoted text -

Thanks, Wayne, for responding to my question!
I don't understand the code you posted here. I tried to modify it to
make sense to me, but I can't seem to get rid of compile errors in
it. How is the DLookup function you used supposed to work? Is the
code supposed to check to see if txtFullname from the form = FullName
from the table, and the same for class? If so, I can't seem to phrase
it right, as Access keeps telling me I'm missing a list separator
or ), or that there is something else wrong. I haven't ever used the
DLookup function before, though I've seen others speak of it here. If
possible, could you explain what it is your code is doing? Your help
is most appreciated.

-George

 

[Wayne-I-M]

Hi George

The basics are that you can "lookup" to see if 2 fields in your table = the
data input in 2 controls your form. If they do you can perform and event -
such as cancelling the input, a message, allow the input, etc etc etc

Can you let me have
The name of the table where your data is
The names of the fields in the table
The names of the form controls that are bound to the fields
The name of the form

If you post these name I will write the code for you - note I am V busy at
work at the mo so it may not be until I get home tonight - although someone
else may also post the code here befor I do

--
Wayne
Manchester, England.

Hi

It's my personal preference but I wouls not delete the record of classes
taken. I wuld store this in a seperate table linked the table contining
students details.

But as you asked (you know your database and I dont).

On your form I assume you have the folloing fields (change the names in the
cde to what they really are).
txtFullName, txtClassName, and txtDateTaken - I also assume that each record
have an ID field called txtID and that all this is stored in a table called
tblDetails

I assume you will fill in the txtFullName 1st and then txtClassName

In the BeforeUpdate event of txtClassName put this

Private Sub CDSurname_AfterUpdate()
If ((DLookup("[txtID]", "[tblDetails]", "[txtFullName] ='" &
Form!txtFullName & "' AND [txtClassName] = '" & Form!txtClassName & "'")))
Then
Beep
MsgBox "This person has already done this class", vbOKOnly, "C;ass
Checker"
Else
Me.txtDateTaken.setfocus
Me.txtDateTaken = Null
End If
End Sub

Of course you can refine this be putting vbyes vbno - cancel = true, etc,
etc, etc after the message, or more normally as part of it - so that an
action is taken to cancel the input or allow it (there may be 2 John Smiths
in the same class)

Good luck

--
Wayne
Manchester, England.

Hello all out there.
I have tried to use this forum trying to solve this problem, and have
not found anything that fits what I need, at least not yet. I have a
form that records class history, containing the three fields of
FullName, ClassName, and DateTaken.

When a user enters values for FullName and ClassName, I want any
existing record with that combination to updated with the new date
provided by the user. This is because I am only interested in the
most recent time any class was taken by a given person.

It would be best if the user didn't edit the existing record but
rather replaced it with the new one, if that makes sense. This is
because I have the date box set up so that they can "lock" the
date(with a button) so that the datee they enter moves to the new
record when they hit my "add new" button.

If this is possible, any help would be greatly appreciated.

TIA,
George- Hide quoted text -

- Show quoted text -

Thanks, Wayne, for responding to my question!
I don't understand the code you posted here. I tried to modify it to
make sense to me, but I can't seem to get rid of compile errors in
it. How is the DLookup function you used supposed to work? Is the
code supposed to check to see if txtFullname from the form = FullName
from the table, and the same for class? If so, I can't seem to phrase
it right, as Access keeps telling me I'm missing a list separator
or ), or that there is something else wrong. I haven't ever used the
DLookup function before, though I've seen others speak of it here. If
possible, could you explain what it is your code is doing? Your help
is most appreciated.

-George

 

[sword856]

Hi George

The basics are that you can "lookup" to see if 2 fields in your table = the
data input in 2 controls your form. If they do you can perform and event -
such as cancelling the input, a message, allow the input, etc etc etc

Can you let me have
The name of the table where your data is
The names of the fields in the table
The names of the form controls that are bound to the fields
The name of the form

If you post these name I will write the code for you - note I am V busy at
work at the mo so it may not be until I get home tonight - although someone
else may also post the code here befor I do

--
Wayne
Manchester, England.

Hi
It's my personal preference but I wouls not delete the record of classes
taken. I wuld store this in a seperate table linked the table contining
students details.
But as you asked (you know your database and I dont).
On your form I assume you have the folloing fields (change the names in the
cde to what they really are).
txtFullName, txtClassName, and txtDateTaken - I also assume that each record
have an ID field called txtID and that all this is stored in a table called
tblDetails
I assume you will fill in the txtFullName 1st and then txtClassName
In the BeforeUpdate event of txtClassName put this
Private Sub CDSurname_AfterUpdate()
If ((DLookup("[txtID]", "[tblDetails]", "[txtFullName] ='" &
Form!txtFullName & "' AND [txtClassName] = '" & Form!txtClassName & "'")))
Then
Beep
MsgBox "This person has already done this class", vbOKOnly, "C;ass
Checker"
Else
Me.txtDateTaken.setfocus
Me.txtDateTaken = Null
End If
End Sub
Of course you can refine this be putting vbyes vbno - cancel = true, etc,
etc, etc after the message, or more normally as part of it - so that an
action is taken to cancel the input or allow it (there may be 2 John Smiths
in the same class)
Good luck
--
Wayne
Manchester, England.
:
Hello all out there.
I have tried to use this forum trying to solve this problem, and have
not found anything that fits what I need, at least not yet. I have a
form that records class history, containing the three fields of
FullName, ClassName, and DateTaken.
When a user enters values for FullName and ClassName, I want any
existing record with that combination to updated with the new date
provided by the user. This is because I am only interested in the
most recent time any class was taken by a given person.
It would be best if the user didn't edit the existing record but
rather replaced it with the new one, if that makes sense. This is
because I have the date box set up so that they can "lock" the
date(with a button) so that the datee they enter moves to the new
record when they hit my "add new" button.
If this is possible, any help would be greatly appreciated.
TIA,
George- Hide quoted text -
- Show quoted text -

Thanks, Wayne, for responding to my question!
I don't understand the code you posted here. I tried to modify it to
make sense to me, but I can't seem to get rid of compile errors in
it. How is the DLookup function you used supposed to work? Is the
code supposed to check to see if txtFullname from the form = FullName
from the table, and the same for class? If so, I can't seem to phrase
it right, as Access keeps telling me I'm missing a list separator
or ), or that there is something else wrong. I haven't ever used the
DLookup function before, though I've seen others speak of it here. If
possible, could you explain what it is your code is doing? Your help
is most appreciated.

-George- Hide quoted text -

- Show quoted text -

Hey Wayne,
I have been out a few days, so I haven't had a chance to respond until
now.

Table: TrainingHistory

Fields: ClassName, FullName

Controls: Classname, FullName

Form: TrainingHistoryForm

The action I would like to take is to ask the user if they want to
replace the old record, then if yes do so, if no cancel the input.
Thanks again for working with me on this.