SQL & Access

B

Bunky

We just recently upsized our tables to SQL and everything is working as it
should with one exception. When the operators enter a new record that would
cause a duplicate key, instead of telling them via an error box, it acts like
it takes the data but does not really add it to the table. Is there anyway
we can get our popup back for the duplicate error at least?

Thanks for your help!!
 
S

strive4peace

Hi Bunky,

if you are using a form to manipulate your data, you can use the form
BeforeUpdate event to validate data

what is your primary key (PK) fieldname? what is its data type?


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
:) have an awesome day :)
*
 
B

Bunky

Hi Crystal!

My PK is three fields; the Employee ID (number field), the associate name
(text) and the Date Entered (Date/Time field). If the supervisor wants to,
they can enter more entry per day, that is why I have included the time. We
have a long term solution (adding a new field to the table of date and time
that will be autofilled by the access) but I was hoping to find a fix for the
interim.

How would the BeforeUpdate event work to validate the data? Not clear on
that one.
 
S

strive4peace

Hi Bunky,

"We have a long term solution (adding a new field to the table of date
and time that will be autofilled by the access)"

no need to make it a long-term solution... use the form BeforeInsert
event (which happens when you first type a character to make a new
record) to fill it...

'~~~~~~~~~~
me.DateTime_controlname = now()
'~~~~~~~~~~

It would be better to do this now and only check for a duplicate in
EmployeeID and AssociateName for this reason:

Access stores date/times in a numeric format where the integer portion
of the number represents the date and the decimal portion of the number
represents time:

Single and Double precision numbers are stored in floating point format
(exponential format: 1.234 x 10^^4) and are susceptable to creating
"Ghost" digits several places out...so they are not accurate for exact
comparisons (even if the data "appears" to have no decimals)

If you want to compare 2 single or double precision values to see if
they are "equal", do something like this:

if ABS(num1-num2) < 0.0001 then

where .0001 would be your tolerance

so, with that said, I am going to leave the date/time out of your check

'~~~~~~~~~~
'first make sure required data is filled

'make sure EmployeeID is filled out
If IsNull(me.EmployeeID) then

'if it is not filled out, then move the focus to that control
me.EmployeeID.setFocus

'give the user a message
msgbox "You must enter EmployeeID",,"Missing Data"

'if this is a combobox, drop the list for them
me.EmployeeID.dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'make sure AssociateName is filled out
If IsNull(me.AssociateName ) then

'if it is not filled out, then move the focus to that control
me.AssociateName.setFocus

'give the user a message
msgbox "You must enter Associate Name",,"Missing Data"

'if this is a combobox, drop the list for them
me.AssociateName.dropDown

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub
end if

'assuming that EmployeeID is a long integer data type
dim mRecordID as long

mRecordID = nz(dLookup("[EmployeeID]", "[Tablename]" _
, "EmployeeID=" & me.EmployeeID _
& " AND AssociateName = '" & me.AssociateName & "'" _
), 0)

if mRecordID <> 0 then

'give the user a message
msgbox "You have entered a duplicate combination " _
& "of EmployeeID and Associate Name" _
,,"Primary Key not unique"

'don't save the record yet
Cancel = true

'quit checking and give them a chance to fill it out
exit sub

end if

'~~~~~~~~~~

WHERE
AssociateName is the fieldname of your Associate Name
EmployeeID is the fieldname of your EmployeeID
Tablename is the name of your table


Warm Regards,
Crystal

Access Basics
8-part free tutorial that covers essentials in Access
http://www.accessmvp.com/Strive4Peace/Index.htm

*
:) have an awesome day :)
*
 
Top