Need some basic help with Access 2000

  • Thread starter Thread starter Rachel
  • Start date Start date
R

Rachel

I am working on an extremely simple database for my
company. This is my first time working with Access 2000
and I'm flying solo with the help of the Access 2000
Bible. I'm stuck, perhaps someone can help.

I have a form where I enter information on our clients.
Each client gets a unique id that is three letters and
four numbers. I have two questions about this.

1. When I enter a new client in the database, how can I
get it to prompt me if an ID is already in use? I have it
set so that duplicates are not allowed already but no
warning appears.

2. When I go to edit a client, I want to call him up by ID
number, how do I make my form do that so that I can type
in the number and then it will go to that record?

Thanks so much for your help.

Regards,
Rachel
 
1 - Try checking for duplicate right away
In the BeforeUpdate event of the control try something like
the following:

if not isnull(dlookup("Field1","myTable","PK1=" & me.pk1)) then
msgbox "Record Already exists!"
cancel=true
endif


The Dlookup will return a null if there is no match so you
are looking
for a Not null condition to indicate a duplicate. If any of
your
fields are text you will have to wrap the value in Quotes
so that the
value is passed to the Dlookup as a literal string.

2 - Insert a combo box on your form. Use the Wizard. That
should retrieve the record selected.

Jim
 
Jim,

Thank you so much for your help. I changed it to a combo
box and based a query on the table to provide the content
for the pull down menu. That works just fine. However, I
can't get the page to change to show the information for
the new client once I choose a different ID number.

Can you explain a little further your answer to my first
question. It was a touch over my head. I have a table that
has all the information on each client and a form that is
based on that table. Where are you suggesting I put this
control? I assume that by Field1 you mean the field name I
have to look in, and that myTable is the table I am using.
What does the rest mean?

Thanks,
Rachel
 
When you created the combo box you were asked to check one
of 3 boxes. You want
"Find a record on my form .....

It inserts code in the after update event that looks
similar to this
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[YourRecordId] = " & Str(Me![Combo29])
Me.Bookmark = rs.Bookmark

Good luck

Jim
 
I did some testing and changed the syntax to the following

If Not IsNull(DLookup("fieldnameintable", "table name", _
"[fieldnameintable]=" & "'" & Me.controlnameonform &
"'")) Then
MsgBox "Record Already exists!"
Cancel = True
End If

Jim
 
Back
Top