I forgot to answer these statements from your last message:
A while back I changed the name of my Table from DK Grand Table
to Affidavits and have changed the name of the 7 digit field from
Affidavit # to REI. (I was trying to eliminate spaces because I thought
that might be a problem).
Spaces and special characters can be a problem. The best strategy is to use a good naming
convention. Here are some links to useful information on this topic. You will be able to avoid
problems that others encounter by using naming conventions and avoiding the use of reserved
words:
Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763
Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp
Using a Naming Convention
http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp
Reserved Words
Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335
List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266
But I'm not sure if the deeper levels of Access are still looking for the
"original" name. For example, when I click on the REI text box in Form
design view it is still called Affidavit # in the blue bar even though I
changed it to REI in the main Table.
When you are displaying the Properties dialog, and you select a control, the *name* of the
control (not it's Control Source) is displayed in the blue title bar. The field that a control is
bound to is the "Control Source", which is shown as the first entry on the Data tab. The name of
the control is shown as the first entry on the Other tab.
Also, it looks like you used txtDK_ID in your code, whereas I used txtREI. Whatever name you
choose for this textbox, make sure that the references to this name match in the VBA code.
Tom
________________________________
Hi Dave,
Copy the code shown below and paste it into the form module for the form that you are using to do
your data entry.
A few quick notes:
1.) I named the textbox used to enter REI numbers: txtREI
2.) The txtREI textbox has the REI field as it's control source
3.) The Form_Current event procedure simply sets focus to the txtREI textbox when you go to add
a new record. It doesn't serve any other purpose.
4.) I used the domain aggregrate function DLookup to determine if the REI was already entered
into the table. This function has the general form: DLookup("Expression", "Domain", "Criteria").
You can select the word DLookup in the code module and press F1 to open up Help on this function.
Of special importance is the following:
If no record satisfies criteria or if domain contains no records, the DLookup function returns a
Null.
You can also check out this link for more information on DLookup. This information applies to the
other domain aggregrate functions (DMax, DMin, DCount, etc) as well:
http://www.mvps.org/access/general/gen0018.htm
5.) The DLookup function is wrapped within the Nz function. This function has the general form:
Nz(Value, ValueIfNull). If DLookup returns a null, then we need to first convert this null to a
string before we can assign the return value to a string variable, strREI. Otherwise, a run-time
error occurs. I am using this function to convert null to a zero-length string (ie. "").
6.) The Msgbox function is used to determine the user's response to a message. The return value
of this function is assigned to the integer variable named intResponse, which is then tested with
an If...Then....End If construct.
From your first message:
Any advice from giving me the exact code to recommending
a book, to recommending someone who does this for money
would be appreciated!!
If you find that my solution saves you a lot of time and frustration with data entry, and if you
were serious about the money part, please consider making a contribution to the Northwest Kidney
Center in Seattle, WA. You can even use PayPal to make a quick and easy donation.
http://www.nwkidney.org/ways2help/donatenow/
Please let me know if this code works for you.
Tom
'********************Begin Code************************
Option Compare Database
Option Explicit
Private Sub Form_Current()
On Error GoTo ProcError
If Me.NewRecord Then
Me.txtREI.SetFocus
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Current..."
Resume ExitProc
End Sub
Private Sub txtREI_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
Dim strREI As String
Dim intResponse As Integer
Dim strMessage As String
Dim strTitle As String
strREI = Nz(DLookup("REI", "Affidavits", "REI = '" & Me.REI & "'"), "")
If Len(strREI) > 0 Then 'Record already exists, so tell user.
strMessage = "This REI value has already been entered." _
& vbCrLf & vbCrLf _
& "Click Yes to move to existing record or" & vbCrLf _
& "click No to continue editing this new record."
strTitle = "Move to existing record?"
intResponse = MsgBox(strMessage, vbCritical + vbYesNo, strTitle)
If intResponse = vbYes Then ' Move to existing record.
Cancel = True
Me.Undo
DoCmd.FindRecord (strREI)
Else
Cancel = True
End If
End If
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure txtREI_BeforeUpdate..."
Resume ExitProc
End Sub
'********************End Code**************************
__________________________________
Hi Dave,
Let me work on this one tomorrow night. I spent a lot of time following up on another question.
Tom
_________________________________
OK...I'll try to show what I've done to try to get this
to work. As a reminder, I'm trying to get transported to
my record if the 7 digit number I enter is already in the
database.
The following is what presently exists in the Before
Update Code builder box........
"Option Compare Database
Private Sub Affidavit___BeforeUpdate(Cancel As Integer)
End Sub
Private Sub Date_Entered_Click()
End Sub
Private Sub Form_Load()
End Sub"................
Here is what I tried pasting into the top 1/3rd of the
Code builder box..........
"Private Sub txtDK_ID_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[DK_ID]", [Affidavits], _
"[DK_ID] = '" & Me!txtDK_ID & "'") Then
MsgBox "This DK_ID has already been entered", vbOKOnly
Cancel = True
End If
End Sub"..........
Well, there you have it. A while back I changed the name
of my Table from DK Grand Table to Affidavits and have
changed the name of the 7 digit field from Affidavit # to
REI. (I was trying to eliminate spaces because I thought
that might be a problem) But I'm not sure if the deeper
levels of Access are still looking for the "original"
name. For example, when I click on the REI text box in
Form design view it is still called Affidavit # in the
blue bar even though I changed it to REI in the main
Table. Woe is me. Good luck helping me. I need it and
I sure appreciate the help!!!
Dave K
-----Original Message-----
Hi Dave,
I recommend using the Before Update event procedure for
the textbox, not the form. In form design
view, click on View > Properties to display the
properties dialog. Select the textbox. You should
see the name of the textbox in the blue title bar of the
properties dialog. Select the Event tab.
Click into the Before_Update procedure. At this point,
you can use the drop down to select Event
Procedure, and then click on the build button (the
button with the three dots). Alternatively,
you can simply click on the build button and select Code Builder.
Right again.
working.
Show us the code that you have entered. Are you getting
any error messages? If so, include the
error number and description.
You can use the domain aggregrate function DLookup in
the Before_Update event procedure for the
textbox, or you can write a procedure that creates a
recordset, which you then search. So, show
us what you have done so far and describe exactly any
error messages that you are receiving.
Tom
________________________________
Hi. I've asked this question before and have received a
good response...but I don't have the
training to fill in any missing pieces and so I'm still
stuck. I have a single Table called,
"Affidavits". The first thing I enter in my Form page
is a 7 digit number which is called ,"REI"
in the Table. In the Table design view, "REI" is set
for text and also set for, "Yes, no
duplicates". I enter hundreds of these Request For
Inspection (REI) forms and I have to "save
the record" after entering the REI to see if it already
exists, or I enter all the other info
such as name, number, address, etc, etc and then to my
frustration I find the record already
exists. It would be great if I could go right to the
record after tabbing out of the first field
(REI) in my form. From previous posts I realize I need
to enter code in the properties box of my
Form in the Before Update section. I believe I'm
supposed to select "Event Procedure", then
click the other box and select Code builder. Here is
where the problem lies. I have spent hours
trying to type in code that will solve my problem. But
it isn't working. Any advice from giving
me the exact code to recommending a book, to
recommending someone who does this for money would
