Validate before adding record
Working using Access 2000, but this will be updated to Access 2010; it's the front-end to a SQL database.
tbl_cont
UID - Alphanumeric, nine-digit, unique for each record
Asset - manually entered number
Startdate - manually entered date
Enddate - manually entered date
Currently, Asset is added with StartDate, usually today, and an EndDate that is about 40 years from now. I've used Patrick's code in http:/Q_27080800.html to generate and add the unique ID number when the record is added.
When an Asset is removed, we edit the record to show the Enddate as the date it is removed. However, it is possible that the Asset can by added back into circulation, for which I will have a new record with a new UID.
What I need to do is make sure that the asset isn't currently "on the floor" . Example:
Asset 1: Start 9/1/2005 End 10/25/2014 ==> This should throw an error and the record, including the UID, should not be allowed to be added.
Asset 1: Start 9/1/2005 End 9/1/1010 ==> This should be added, with a new UID, because even though it's the same Asset, the date has expired.
If an Asset is not in the table, then it should be added with a UID.
What I'm hung up on is how to validate the dates from the table before I actually try to add the record. There are around 14,000 records currently in the table, and it's usually no more than half a dozen that need to be added or edited daily.
Thanks,
ep
tbl_cont
UID - Alphanumeric, nine-digit, unique for each record
Asset - manually entered number
Startdate - manually entered date
Enddate - manually entered date
Currently, Asset is added with StartDate, usually today, and an EndDate that is about 40 years from now. I've used Patrick's code in http:/Q_27080800.html to generate and add the unique ID number when the record is added.
When an Asset is removed, we edit the record to show the Enddate as the date it is removed. However, it is possible that the Asset can by added back into circulation, for which I will have a new record with a new UID.
What I need to do is make sure that the asset isn't currently "on the floor" . Example:
Asset 1: Start 9/1/2005 End 10/25/2014 ==> This should throw an error and the record, including the UID, should not be allowed to be added.
Asset 1: Start 9/1/2005 End 9/1/1010 ==> This should be added, with a new UID, because even though it's the same Asset, the date has expired.
If an Asset is not in the table, then it should be added with a UID.
What I'm hung up on is how to validate the dates from the table before I actually try to add the record. There are around 14,000 records currently in the table, and it's usually no more than half a dozen that need to be added or edited daily.
Thanks,
ep
mbizup
Are you trying to reject End Dates that are in the future, or are you trying to validate the date range as a whole?
ASKER
Good question. I guess it's actually just the end date.
If Asset1013 has an end date in the future, then it's being used, so it can't be used someplace else at the same time... BUT...
If Asset1013 has an end date six months ago, then it can be used again -- at which point I want to be able to add a new record with a new UniqueID.
An asset could be used for six months in 2005, four months in 2006, two months in early 2006 and late 2006 and so on.
Does that help?
If Asset1013 has an end date in the future, then it's being used, so it can't be used someplace else at the same time... BUT...
If Asset1013 has an end date six months ago, then it can be used again -- at which point I want to be able to add a new record with a new UniqueID.
An asset could be used for six months in 2005, four months in 2006, two months in early 2006 and late 2006 and so on.
Does that help?
I think this is the logic you're looking for - in your form's Before Update event:
The first check is what I think you need... the others are additional checks that may or may not be relevant.
The first check is what I think you need... the others are additional checks that may or may not be relevant.
Private Sub Form_BeforeUpdate(Cancel As Integer)
' General sanity check
If me.txtStartDate > Me.txtEndDate then
msgbox "Invalid date range. Start date should be less than or equal to end date
cancel = true
exit sub
end if
' Check for currently on the floor
If Me.txtStartDate <= Date() and Me.txtEndDate >= Date() then
' Error message and cancel update
msgBox "Currently 'on the floor'"
Cancel = True
' Both dates are after today
ElseIf Me.txtStartDate >= Date() and Me.txtEndDate >= Date()
' Start and end dates are both in the future
' Both dates are earlier than today
ElseIf Me.txtStartDate <= Date() and Me.txtEndDate <= Date()
' Both dates are in the past
Else
' etc
end if
End Sub
That shows the general logic for the validation check, but from what you're describing I'm not sure that the form's before update event is the right place for it.
Do you have existing code such as a command button click event for doing these updates? If so, I think the validation code should probably be integrated with that.
Do you have existing code such as a command button click event for doing these updates? If so, I think the validation code should probably be integrated with that.
ASKER
I don't have it yet, but the act of completing the form would be the same as (for example) a "Save and Close", wouldn't it? So if I'm using the form in data entry mode, if I tried to add the record (by going to the new blank record), it should give me an error... If I'm reading your code correctly.
I'll play with it and will see what happens.
I'll play with it and will see what happens.
ASKER
I know this is 2000, but I don't have 2010 handy. Would this be "Before Update" or "Before Insert"?
Try it with the Before Update event if we're dealing with existing records.
Before Insert applies to new records.
Before Insert applies to new records.
ASKER
Nevermind that last question... I see from the code itself.
Should the date fields in the table be text? Or will Access read the date field as if it's text
Should the date fields in the table be text? Or will Access read the date field as if it's text
Me.txtStartDate
Missed your previous comment.
Actually if you're dealing with a Data Entry form (new records), use the Before Insert event.
Actually if you're dealing with a Data Entry form (new records), use the Before Insert event.
<<
Should the date fields in the table be text? Or will Access read the date field as if it's text
>>
Use date/time fields.
EDIT:
Me.txtStartDate and Me.txtEndDate in the code I posted are just hypothetical names of textboxes on your form. The underlying fields should be dates.
Should the date fields in the table be text? Or will Access read the date field as if it's text
>>
Use date/time fields.
EDIT:
Me.txtStartDate and Me.txtEndDate in the code I posted are just hypothetical names of textboxes on your form. The underlying fields should be dates.
ASKER
I'm getting an error:
Third line:If Me.txtStartDate > Me.txtEndDate Then
Error is Compile error: Method or data member not found.
Third line:If Me.txtStartDate > Me.txtEndDate Then
Error is Compile error: Method or data member not found.
Try replacing txtStartDate and txtEndDate with the actual names of the textboxes you are using to enter the dates.
ASKER
Okay, that worked to get rid of that... but now I'm getting Already On Floor messages, and I'm not getting the UID.
The file is uploaded.
tbl_Contribution is the table I was sent, and it's a dump from the SQL database. It's included just so there is data to copy from, and serves no other purpose.
tbl_egp_cont is the table I'm working with; so far, I've added on record of information copied from the other table.
The Add To Contribution form is my little brother's first attempt at doing this. It can be ignored.
frm_egp_cont is the form I'm working on. Your code above is an Event Procedure attached to the Before Insert action; Patrick's is an Event Procedure attached to the uid field on the form at On Lost Focus.
LVAPCDI01ep.mdb
The file is uploaded.
tbl_Contribution is the table I was sent, and it's a dump from the SQL database. It's included just so there is data to copy from, and serves no other purpose.
tbl_egp_cont is the table I'm working with; so far, I've added on record of information copied from the other table.
The Add To Contribution form is my little brother's first attempt at doing this. It can be ignored.
frm_egp_cont is the form I'm working on. Your code above is an Event Procedure attached to the Before Insert action; Patrick's is an Event Procedure attached to the uid field on the form at On Lost Focus.
LVAPCDI01ep.mdb
Those two blocks of code need to be integrated. The problem is that the Lost Focus event is actually creating the new record... which is making the Before Insert event fire earlier than you want it to.
Try moving both blocks of code to the form's Before Update event, with Patrick's code to create the UID placed after the validation checks. The 'feel' isn't quite the same, but the functionality is closer to what you're looking for:
Try moving both blocks of code to the form's Before Update event, with Patrick's code to create the UID placed after the validation checks. The 'feel' isn't quite the same, but the functionality is closer to what you're looking for:
Private Sub Form_BeforeUpdate(Cancel As Integer)
' General sanity check
If Me.initial > Me.final Then
MsgBox "Invalid date range. Initial date should be less than or equal to end date"
Cancel = True
Exit Sub
End If
' Check for currently on the floor
If Me.initial <= Date And Me.final >= Date Then
' Error message and cancel update
MsgBox "Currently 'on the floor'"
Cancel = True
Me.Undo
Exit Sub
' Both dates are after today
Rem ElseIf Me.initial >= Date() and Me.final >= Date()
' Start and end dates are both in the future
' Both dates are earlier than today
Rem ElseIf Me.initial <= Date() and Me.final <= Date()
' Both dates are in the past
Rem Else
' etc
End If
' Create the UID after the validation checks
Dim keyStr As String, output As String, s As Integer
keyStr = "0123456789abcdefghijklmnpqrstuvwsyzABCDEFGHIJKLMNPQRSTUVWXYZ"
Randomize
output = ""
Do While Len(output) < 9
s = Int(Rnd * Len(keyStr)) + 1
output = output & Mid$(keyStr, s, 1)
Loop
Me.uid = output
End Sub
Hang on... I've got a better idea, which keeps the same feel you initially had...
1)
Revise Patrick's code like this to make it a function which returns a value and place it in a seperate module (and remove the Lost Focus Event):
2)
Place this in the Default Value property of the UID textbox:
GetUID()
3)
Use this code in the form's BeforeUpdate event:
1)
Revise Patrick's code like this to make it a function which returns a value and place it in a seperate module (and remove the Lost Focus Event):
Function GetUID() As String
Dim keyStr As String, output As String, s As Integer
keyStr = "0123456789abcdefghijklmnpqrstuvwsyzABCDEFGHIJKLMNPQRSTUVWXYZ"
Randomize
output = ""
Do While Len(output) < 9
s = Int(Rnd * Len(keyStr)) + 1
output = output & Mid$(keyStr, s, 1)
Loop
GetUID = output
End Function2)
Place this in the Default Value property of the UID textbox:
GetUID()
3)
Use this code in the form's BeforeUpdate event:
Private Sub Form_BeforeUpdate(Cancel As Integer)
' General sanity check
If Me.initial > Me.final Then
MsgBox "Invalid date range. Initial date should be less than or equal to end date"
Cancel = True
Exit Sub
End If
' Check for currently on the floor
If Me.initial <= Date And Me.final >= Date Then
' Error message and cancel update
MsgBox "Currently 'on the floor'"
Cancel = True
Me.Undo
Exit Sub
' Both dates are after today
Rem ElseIf Me.initial >= Date() and Me.final >= Date()
' Start and end dates are both in the future
' Both dates are earlier than today
Rem ElseIf Me.initial <= Date() and Me.final <= Date()
' Both dates are in the past
Rem Else
' etc
End If
End SubASKER
There's still a problem.
I entered a record for an asset with the initial date of 9/1/2010 and a final date of 4/18/2012. No problem -- everything worked fine.
Then I entered a record for the same asset with an initial date of 4/19/2012 and an end date of 12/31/2048 -- and was told it is still on the floor. I checked the table, and the second record wasn't added... but it should have been.
I'll be out Tuesday am, but will be back in the afternoon.
I entered a record for an asset with the initial date of 9/1/2010 and a final date of 4/18/2012. No problem -- everything worked fine.
Then I entered a record for the same asset with an initial date of 4/19/2012 and an end date of 12/31/2048 -- and was told it is still on the floor. I checked the table, and the second record wasn't added... but it should have been.
I'll be out Tuesday am, but will be back in the afternoon.
My understanding of 'On the floor' is that today's date falls between the StartDate and the EndDate, so I would expect that record to be rejected because 9/24/2012 is between 9/1/2010 and 4/18/2012.
Is my definition of 'on the floor' wrong, or am I missing something else?
Is my definition of 'on the floor' wrong, or am I missing something else?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Is my definition of 'on the floor' wrong, or am I missing something else?
The Assets in question are machines that get deployed throughout several buildings. An Asset can be deployed ("initial") for a period of time, say six months, during which time it is "on the floor". At the end of that time period it is retired for a period of time ("final"). They use the 2048 date to show that they don't know when it will be retired; they only modify the "final" date when the machine is actually taken off the floor.
But it can be redeployed in either the same building or a different one -- which means there's a new record with a new UID, new "initial" date and a new "final" date. So, what I want to do is check, using the Asset ID number, whether it is shown as still being on the floor (which will mean someone has done something wrong). If the machine is not on the floor, then it gets added.
To all appearances, your latest update using Jim's article worked like a charm.
The Assets in question are machines that get deployed throughout several buildings. An Asset can be deployed ("initial") for a period of time, say six months, during which time it is "on the floor". At the end of that time period it is retired for a period of time ("final"). They use the 2048 date to show that they don't know when it will be retired; they only modify the "final" date when the machine is actually taken off the floor.
But it can be redeployed in either the same building or a different one -- which means there's a new record with a new UID, new "initial" date and a new "final" date. So, what I want to do is check, using the Asset ID number, whether it is shown as still being on the floor (which will mean someone has done something wrong). If the machine is not on the floor, then it gets added.
To all appearances, your latest update using Jim's article worked like a charm.
ASKER
What's unfortunate is that I can't give you (and Jim) more points for this. At least one company in Las Vegas thanks you!
Glad that worked out.
One of these days I'm going to get out to Las Vegas with a pre-determined spending limit. We had an unexpected 24 hour stay there a few years ago after missing a connecting flight to Oregon. It looked like it would be a fun place for a long weekend getaway.
One of these days I'm going to get out to Las Vegas with a pre-determined spending limit. We had an unexpected 24 hour stay there a few years ago after missing a connecting flight to Oregon. It looked like it would be a fun place for a long weekend getaway.
Hello:
I'm the little (well, youngest) brother in question. Thank you to the big (well, oldest) brother, mbizup and Jim D. for all the most capable assistance. My knowledge of Access/VB is such that much of the above looks like Esperanto to me, so hopefully I will implement it correctly.
Should you decide to visit our fair city, give ericpete the dates as far in advance as possible, and I'll attempt to get something for you (a dinner, room, etc.) comped somewhere here in town.
I'm the little (well, youngest) brother in question. Thank you to the big (well, oldest) brother, mbizup and Jim D. for all the most capable assistance. My knowledge of Access/VB is such that much of the above looks like Esperanto to me, so hopefully I will implement it correctly.
Should you decide to visit our fair city, give ericpete the dates as far in advance as possible, and I'll attempt to get something for you (a dinner, room, etc.) comped somewhere here in town.
Hey - It's the younger brother! Great to see you out here.
Thanks for the nice comment and I will definitely let ep know when we are heading out there.
Thanks for the nice comment and I will definitely let ep know when we are heading out there.
ASKER
Phase 2 of this little project: https://www.experts-exchange.com/questions/27884942/Validate-before-adding-record-part-2.html
Assistance greatly appreciated.
Assistance greatly appreciated.