I need some help with a MS Access 97 database!
2 отговора
177 прегледа
I need some help with a MS Access 97 database!
I've been trying to create a database in MS Access 97 for an year now, but I've reached one point from which I can't go ahead :( Seems that my knowledge is not enough, so I decided to ask you for some help.
Please give some ideas, girls and guys!
About the database:
There is one field on the form which is read-only called CaseNumber.
Next there are three fields which are for input. They are in the order CaseNumberYear, CaseNumberFacility, and then CaseNumberNumber (creative names, huh?).
When the user inputs each ending with the CaseNumberNumber field, they will Tab out or click out. In any case, all three fields contain information and the CaseNumberNumber field loses focus. When this happens, an event it triggered under the LostFocus trigger runs the code listed below.
The first thing that is done: the three fields are concatinated into one field -- CaseNumber (essentially, CaseNumber = CaseNumberYear & CaseNumberFacility & CaseNumberNumber). I have tested this in different ways including the use of code instead of macros. Both work fine and give the correct results.
Once these are concatinated, CaseNumber has a value which is the Primary Key.
Next, I want to compare the concatination of the value the user have entered with all the others stored in the database (a field called CaseNumber in the Table called Cases) to ensure they are not the same. (I know Access would eventually do this automatically - and it does... But, I need it to check for duplicates when it exits this CaseNumberNumber field).
I have tried the DLookUp() statement shown below to check this, but it keeps bringing up the following error message:
Run-time error '64479':
The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'TSD_.''
"TSD_" is the data I have typed into the CaseNumberFacility field. If I type anything in that field, that is what it will say: 'doesn't contain the Automation object' (for instance, if the field CaseNumberFacility contains "JACK", then it would say 'The object doesn't contain the Automation object 'JACK.'').
I really do not understand the error. Looking up error 64479 was no real help to me.
Also, it appears to me (by watches and inserting msgbox() checks for variable's values) that the DLookUp() is picking up an existing record "2002-IYC_-0029". This is an existing record, but it is not a match to what is in the Me.CaseNumber field. Help says that DLookUp() returns null if no match is found. What is going on here? I am self-taught, so I could be missing something simple (like syntax)...
I did think about forcing it to update when it exited the field so it would automatically do a check for me, but there are other required fields which are empty at the time this check must occur, so that will not work.
Below, I have listed the VBA code from the Form properties, and then both Macros that are referenced in the VBA code. I know some of the Macro info is not important to this question, but at least you can verify for yourself that they are not the cause of the problem. I wish it were Access 2000 or so, but this is what the state requires for compatibility right now. :)
*****************
Here is the code:
*****************
Public Sub CaseNumberNumberIN_LostFocus()
DoCmd.Hourglass True
DoCmd.RunMacro ("CaseNumberEntryMacro")
If IsNull(DLookup("[CaseNumber]", "Cases", "[CaseNumber] = " & Me.CaseNumber)) Then
DoCmd.RunMacro ("CaseNumberInvalidMacro")
Me.Repaint
DoCmd.Hourglass False
Exit Sub
End If
Me.Repaint
DoCmd.Hourglass False
End Sub
CaseNumberEntryMacro
1.
Action = SetValue
Item = [Forms]![Standard Entry Form]![CaseNumberIN]
Expression = [Forms]![Standard Entry Form]![CaseNumberYearIN] & "-" & [Forms]![Standard Entry Form]![CaseNumberFacilityIN] & "-" & [Forms]![Standard Entry Form]![CaseNumberNumberIN]
2.
Action = RepaintObject
Object Type = Form
Object Name = Standard Entry Form
CaseNumberInvalidMacro
1.
Action = MsgBox
Message = This Case Number is already in use by another record. Please increment your number before continuing.
Beep = Yes
Type = Information
Title = Duplicate Case Number
2.
Action = GoToControl
Control Name = CaseNumberNumber
*****************
Thank you again for reading this too long matter and for the help you will eventually provide :)
Please give some ideas, girls and guys!
About the database:
There is one field on the form which is read-only called CaseNumber.
Next there are three fields which are for input. They are in the order CaseNumberYear, CaseNumberFacility, and then CaseNumberNumber (creative names, huh?).
When the user inputs each ending with the CaseNumberNumber field, they will Tab out or click out. In any case, all three fields contain information and the CaseNumberNumber field loses focus. When this happens, an event it triggered under the LostFocus trigger runs the code listed below.
The first thing that is done: the three fields are concatinated into one field -- CaseNumber (essentially, CaseNumber = CaseNumberYear & CaseNumberFacility & CaseNumberNumber). I have tested this in different ways including the use of code instead of macros. Both work fine and give the correct results.
Once these are concatinated, CaseNumber has a value which is the Primary Key.
Next, I want to compare the concatination of the value the user have entered with all the others stored in the database (a field called CaseNumber in the Table called Cases) to ensure they are not the same. (I know Access would eventually do this automatically - and it does... But, I need it to check for duplicates when it exits this CaseNumberNumber field).
I have tried the DLookUp() statement shown below to check this, but it keeps bringing up the following error message:
Run-time error '64479':
The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'TSD_.''
"TSD_" is the data I have typed into the CaseNumberFacility field. If I type anything in that field, that is what it will say: 'doesn't contain the Automation object' (for instance, if the field CaseNumberFacility contains "JACK", then it would say 'The object doesn't contain the Automation object 'JACK.'').
I really do not understand the error. Looking up error 64479 was no real help to me.
Also, it appears to me (by watches and inserting msgbox() checks for variable's values) that the DLookUp() is picking up an existing record "2002-IYC_-0029". This is an existing record, but it is not a match to what is in the Me.CaseNumber field. Help says that DLookUp() returns null if no match is found. What is going on here? I am self-taught, so I could be missing something simple (like syntax)...
I did think about forcing it to update when it exited the field so it would automatically do a check for me, but there are other required fields which are empty at the time this check must occur, so that will not work.
Below, I have listed the VBA code from the Form properties, and then both Macros that are referenced in the VBA code. I know some of the Macro info is not important to this question, but at least you can verify for yourself that they are not the cause of the problem. I wish it were Access 2000 or so, but this is what the state requires for compatibility right now. :)
*****************
Here is the code:
*****************
Public Sub CaseNumberNumberIN_LostFocus()
DoCmd.Hourglass True
DoCmd.RunMacro ("CaseNumberEntryMacro")
If IsNull(DLookup("[CaseNumber]", "Cases", "[CaseNumber] = " & Me.CaseNumber)) Then
DoCmd.RunMacro ("CaseNumberInvalidMacro")
Me.Repaint
DoCmd.Hourglass False
Exit Sub
End If
Me.Repaint
DoCmd.Hourglass False
End Sub
CaseNumberEntryMacro
1.
Action = SetValue
Item = [Forms]![Standard Entry Form]![CaseNumberIN]
Expression = [Forms]![Standard Entry Form]![CaseNumberYearIN] & "-" & [Forms]![Standard Entry Form]![CaseNumberFacilityIN] & "-" & [Forms]![Standard Entry Form]![CaseNumberNumberIN]
2.
Action = RepaintObject
Object Type = Form
Object Name = Standard Entry Form
CaseNumberInvalidMacro
1.
Action = MsgBox
Message = This Case Number is already in use by another record. Please increment your number before continuing.
Beep = Yes
Type = Information
Title = Duplicate Case Number
2.
Action = GoToControl
Control Name = CaseNumberNumber
*****************
Thank you again for reading this too long matter and for the help you will eventually provide :)
Re: I need some help with a MS Access 97 database!
There is very easy way to check for dublicate records.
Use the Indexes window in table Design view and create multiple-field index!
If the index consists of more than one field, duplicate values can occur in each field, but each combination of values from all the indexed fields must be unique.
Create new index. Name it Unique and add fields CaseNumberNumber, CaseNumberYear, CaseNumberFacility. In Index properties set Unique to Yes!
About your code:
You use the DLookup function to find the value but there is mistake in criteria expresion -
Use DLookup("[CaseNumber]", "Cases", "[CaseNumber] = '" & Me.CaseNumber & "'" )) !
Don't forget quotation marks in "[CaseNumber] = '" & Me.CaseNumber & "'"
Good Luck!
Misho.
Use the Indexes window in table Design view and create multiple-field index!
If the index consists of more than one field, duplicate values can occur in each field, but each combination of values from all the indexed fields must be unique.
Create new index. Name it Unique and add fields CaseNumberNumber, CaseNumberYear, CaseNumberFacility. In Index properties set Unique to Yes!
About your code:
You use the DLookup function to find the value but there is mistake in criteria expresion -
Use DLookup("[CaseNumber]", "Cases", "[CaseNumber] = '" & Me.CaseNumber & "'" )) !
Don't forget quotation marks in "[CaseNumber] = '" & Me.CaseNumber & "'"
Good Luck!
Misho.