form design

  • Thread starter Thread starter Martin
  • Start date Start date
M

Martin

I have a form that contains the following fields Name,
Last Name, Employee#, Module. Is there a way I can prompt
the user through a message box that a particular Employee
already exist, and after which I would like to give the
user the ability to bring up the record on the form. For
example I'm thinking of a message box that pops up and
prompts "This record already exist. Would you like to view
record? Yes/No.
Your suggestions are appreciated.

Martin
 
Hi Martin,

First thing you need to do is change the field named 'Name' to 'FirstName'
or something else because 'Name' is a reserved word in Access and sooner or
later, you will have a problem resulting from the conflict with your field
name and an Access reserved word.

Which fields are you using to determine whether the employee already exists?
You can use the Dcount function with a simple or complex criteria (depending
on your defination of a duplicate employee). Let's say you are using
FirstName, LastName (you wouldn't be able to have two Harry Smiths). The
following code in the BeforeUpdate event of the Firstname field would check
for the duplicate then offer to take you to the matching record.

Private Sub FirstName_BeforeUpdate(Cancel As Integer)
Dim varEmpNum As Variant

varEmpNum = DLookup("[Employee#]", "tblEmployees", _
"FirstName=""" & Me.FirstName & """ AND LastName=""" & Me.LastName &
"""")
If Not IsNull(varEmpNum) Then
'the employee already exists
If MsgBox("An Employee with this name already exists, would you like to
go to this record?", vbYesNo) = vbYes Then
'cancel update
Cancel = True
'undo the changes to this record
Me.Undo
'go to matching record
With Me.RecordsetClone
.FindFirst "[Employee#]=" & varEmpNum
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End Sub

Some things to note about the above code -

1) it is just a starting point for you.

2)Notice that I changed the field names from 'Name' to 'FirstName' and 'Last
Name' to 'LastName'. It is so much easier to work with fieldnames that do
not have embedded spaces. I kept the 'Employee#' field as you had it to show
that with special characters in a field (or control) name, you must bracket
the name. If you don't want to change your field names you will need to
bracket them within the Dlookup statement.

Feel free to post back if you have other questions -
 
Sandra, Thank you for your reply to my question. I have
done the required name changes (FirstName, LastName,
Employee#. My unique identifier would be the Employee#. In
regards to the code would I replace the beginning portion
of the code with Emplyoyee# instead of FirstName?
-----Original Message-----
Hi Martin,

First thing you need to do is change the field named 'Name' to 'FirstName'
or something else because 'Name' is a reserved word in Access and sooner or
later, you will have a problem resulting from the conflict with your field
name and an Access reserved word.

Which fields are you using to determine whether the employee already exists?
You can use the Dcount function with a simple or complex criteria (depending
on your defination of a duplicate employee). Let's say you are using
FirstName, LastName (you wouldn't be able to have two Harry Smiths). The
following code in the BeforeUpdate event of the Firstname field would check
for the duplicate then offer to take you to the matching record.

Private Sub FirstName_BeforeUpdate(Cancel As Integer)
Dim varEmpNum As Variant

varEmpNum = DLookup("[Employee#]", "tblEmployees", _
"FirstName=""" & Me.FirstName & """ AND LastName=""" & Me.LastName &
"""")
If Not IsNull(varEmpNum) Then
'the employee already exists
If MsgBox("An Employee with this name already exists, would you like to
go to this record?", vbYesNo) = vbYes Then
'cancel update
Cancel = True
'undo the changes to this record
Me.Undo
'go to matching record
With Me.RecordsetClone
.FindFirst "[Employee#]=" & varEmpNum
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End Sub

Some things to note about the above code -

1) it is just a starting point for you.

2)Notice that I changed the field names from 'Name' to 'FirstName' and 'Last
Name' to 'LastName'. It is so much easier to work with fieldnames that do
not have embedded spaces. I kept the 'Employee#' field as you had it to show
that with special characters in a field (or control) name, you must bracket
the name. If you don't want to change your field names you will need to
bracket them within the Dlookup statement.

Feel free to post back if you have other questions -
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have a form that contains the following fields Name,
Last Name, Employee#, Module. Is there a way I can prompt
the user through a message box that a particular Employee
already exist, and after which I would like to give the
user the ability to bring up the record on the form. For
example I'm thinking of a message box that pops up and
prompts "This record already exist. Would you like to view
record? Yes/No.
Your suggestions are appreciated.

Martin
Click to expand...

.
Click to expand...
 
Sandra Help! First of all I changed the field names to
FirstName, LastName, Employee. My unique number is
Employee, I added the code you gave me to the BeforeUpdate
event of the Employee field and it works great, but when I
click on "YES" I would like to go to this record it gives
me a run-time error 3070. Here is the code, as you will
see I changed it, I'm not sure what I'm missing. Please
help.

Private Sub FirstName_BeforeUpdate(Cancel As Integer)
Dim varEmpNum As Variant

varEmpNum = DLookup("FirstName", "tblContacts", _
"Employee=""" & """")
If Not IsNull(varEmpNum) Then
'the employee already exists
If MsgBox("An Employee with this name already exists,
would you like to
go to this record?", vbYesNo) = vbYes Then
'cancel update
Cancel = True
'undo the changes to this record
Me.Undo
'go to matching record
With Me.RecordsetClone
.FindFirst "FirstName=" & varEmpNum
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End Sub
-----Original Message-----
Hi Martin,

First thing you need to do is change the field named 'Name' to 'FirstName'
or something else because 'Name' is a reserved word in Access and sooner or
later, you will have a problem resulting from the conflict with your field
name and an Access reserved word.

Which fields are you using to determine whether the employee already exists?
You can use the Dcount function with a simple or complex criteria (depending
on your defination of a duplicate employee). Let's say you are using
FirstName, LastName (you wouldn't be able to have two Harry Smiths). The
following code in the BeforeUpdate event of the Firstname field would check
for the duplicate then offer to take you to the matching record.

Private Sub FirstName_BeforeUpdate(Cancel As Integer)
Dim varEmpNum As Variant

varEmpNum = DLookup("[Employee#]", "tblEmployees", _
"FirstName=""" & Me.FirstName & """ AND LastName=""" & Me.LastName &
"""")
If Not IsNull(varEmpNum) Then
'the employee already exists
If MsgBox("An Employee with this name already exists, would you like to
go to this record?", vbYesNo) = vbYes Then
'cancel update
Cancel = True
'undo the changes to this record
Me.Undo
'go to matching record
With Me.RecordsetClone
.FindFirst "[Employee#]=" & varEmpNum
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End Sub

Some things to note about the above code -

1) it is just a starting point for you.

2)Notice that I changed the field names from 'Name' to 'FirstName' and 'Last
Name' to 'LastName'. It is so much easier to work with fieldnames that do
not have embedded spaces. I kept the 'Employee#' field as you had it to show
that with special characters in a field (or control) name, you must bracket
the name. If you don't want to change your field names you will need to
bracket them within the Dlookup statement.

Feel free to post back if you have other questions -
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have a form that contains the following fields Name,
Last Name, Employee#, Module. Is there a way I can prompt
the user through a message box that a particular Employee
already exist, and after which I would like to give the
user the ability to bring up the record on the form. For
example I'm thinking of a message box that pops up and
prompts "This record already exist. Would you like to view
record? Yes/No.
Your suggestions are appreciated.

Martin
Click to expand...

.
Click to expand...
 
Hi Martin, you've forgotten to include the test value in the criteria part
of the Dlookup -

varEmpNum = DLookup("FirstName", "tblContacts", _
"Employee=""" & me.employee & """")

Note that if Employee is a numeric field then you don't need the additional
quotes:

varEmpNum = DLookup("FirstName", "tblContacts", _
"Employee=" & me.employee & ")

One other little thing, is the user entering the employee number or the
first name? Looking at the above I'm assuming that they are entering the
Employee Number. If so, you should also change the .findfirst to use
Employee:

.FindFirst "Employee=" & me.employee

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Sandra Help! First of all I changed the field names to
FirstName, LastName, Employee. My unique number is
Employee, I added the code you gave me to the BeforeUpdate
event of the Employee field and it works great, but when I
click on "YES" I would like to go to this record it gives
me a run-time error 3070. Here is the code, as you will
see I changed it, I'm not sure what I'm missing. Please
help.

Private Sub FirstName_BeforeUpdate(Cancel As Integer)
Dim varEmpNum As Variant

varEmpNum = DLookup("FirstName", "tblContacts", _
"Employee=""" & """")
If Not IsNull(varEmpNum) Then
'the employee already exists
If MsgBox("An Employee with this name already exists,
would you like to
go to this record?", vbYesNo) = vbYes Then
'cancel update
Cancel = True
'undo the changes to this record
Me.Undo
'go to matching record
With Me.RecordsetClone
.FindFirst "FirstName=" & varEmpNum
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End Sub
-----Original Message-----
Hi Martin,

First thing you need to do is change the field named 'Name' to 'FirstName'
or something else because 'Name' is a reserved word in Access and
sooner or later, you will have a problem resulting from the conflict
with your field name and an Access reserved word.

Which fields are you using to determine whether the employee already
exists? You can use the Dcount function with a simple or complex
criteria (depending on your defination of a duplicate employee).
Let's say you are using FirstName, LastName (you wouldn't be able to
have two Harry Smiths). The following code in the BeforeUpdate
event of the Firstname field would check for the duplicate then
offer to take you to the matching record.

Private Sub FirstName_BeforeUpdate(Cancel As Integer)
Dim varEmpNum As Variant

varEmpNum = DLookup("[Employee#]", "tblEmployees", _
"FirstName=""" & Me.FirstName & """ AND LastName=""" &
Me.LastName & """")
If Not IsNull(varEmpNum) Then
'the employee already exists
If MsgBox("An Employee with this name already exists, would you
like to go to this record?", vbYesNo) = vbYes Then
'cancel update
Cancel = True
'undo the changes to this record
Me.Undo
'go to matching record
With Me.RecordsetClone
.FindFirst "[Employee#]=" & varEmpNum
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
End If
End If
End Sub

Some things to note about the above code -

1) it is just a starting point for you.

2)Notice that I changed the field names from 'Name' to 'FirstName' and 'Last
Name' to 'LastName'. It is so much easier to work with fieldnames
that do not have embedded spaces. I kept the 'Employee#' field as
you had it to show that with special characters in a field (or
control) name, you must bracket the name. If you don't want to
change your field names you will need to bracket them within the
Dlookup statement.

Feel free to post back if you have other questions -
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
I have a form that contains the following fields Name,
Last Name, Employee#, Module. Is there a way I can prompt
the user through a message box that a particular Employee
already exist, and after which I would like to give the
user the ability to bring up the record on the form. For
example I'm thinking of a message box that pops up and
prompts "This record already exist. Would you like to view
record? Yes/No.
Your suggestions are appreciated.

Martin
Click to expand...

.
Click to expand...
Click to expand...
 
Back
Top