Ok, I hate posting these types of questions to the board, but I can’t find anything that will even give me a general direction to go in to figure it out for myself, any hints or help would be greatly appriciated.
I have been asked to design a database that will be able to act as a resource scheduler, with a pop up box saying if that particular resource has already been requested. Not a problem, I thought to myself, I know I have a lot of DATE fields that I can use and some nifty functions that will let me be able to compare them and tell me if they are in conflict. And this is true for one record in a table, but I can’t find anything that will let me compare the dates and times for all records in a table and tell me if they’re in conflict. Does anyone have any ideas on how I would do this?
I haven’t been able to find anything that gives me an idea of how to proceed, but I can’t imagin that it’s impossible.
I’m not entirely sure what you’re asking. If you want to find out which records in a table have the same date and time as the current record, you could create a select query with the date criteria being the value of the date in the current form.
For example, say you have a table called [Ukulele_Ike] with date field [foo], and a data entry form called [Wally] with the date field entered into a textbox called [bar]. In the SQL code of a query called [Atrael] enter “select count(*) as [derfel] from [Ukulele_Ike] where [foo] = [Forms]![Wally]![bar]”. In the After Update Event of [bar], run [Atrael] and check if [derfel] is >= 1. If it is, then you have a conflict.
Of course, I may be totally misunderstanding what you want. Also note that I haven’t tested the above, so it may not work (but it should, I think…)
Sorry, I should have gone into more detail. What I’m trying to do is make it so that when they’re using a form to enter a date and time for a reservation, it will automatically check the entire table and see if there are any conflicts. So that if they try and reserve a room for a time when someone else has secured it, it would come back and give them an error message. I know I can do it with a query, but not sure how to do it right from the form. I suppose I can make the “UponUpdate” even run the query, and give them a message, but that seems kind of a round about method. Thanks again for any help you can give.
Atreal, you can use the BeforeUpdate event of the control you are using to input the date. Run code in this event to check for any conflicts. Maybe execute a query and see if it returns any records. If it doesn’t, your date is valid.
Thanks for the suggestion. Of course, since I have a VB learning deficiancy, writing the code is beyond my capabilities…Maybe I can figure out how to write a macro, then convert it to code. sigh I’ve been putting off learning VB for a while, because it’s not really my job, but it’s getting to the point where I really need to take the time to learn.
Atreal, if you already know how to write the query, there are plenty of examples in the Access help to show you how to execute the query from code. See the Execute method.
Using the query I wrote above, here’s some code to execute it - put it in the BeforeUpdate or AfterUpdate event of your control:
Dim db as Database
Dim rs as RecordSet
Set db = CurrentDB
Set rs = db.OpenRecordset ("Atrael")
if rs![derfel] >= 1 then
MsgBox "Put your conflict message here"
end if
rs.Close
db.Close
That should work, but again, it hasn’t been tested.
Ok, got the count SQL statement to work, that was no problem, I wish I would have thought of that myself…But I’m having trouble getting the code to work. It doesn’t like opening a recordset from a query. Is there any other way of checking to see if the value of the field is 0? I tried it in a macro as a condition, but it really didn’t like that. Any other suggestions?..I’m beggining to dislike this project.
Do you absolutely, positively HAVE to do it in Access? If not, do yourself a BIG favor: get yourself a copy of FileMaker Pro. It will take you perhaps an hour from the time you open the box to deploy your finished solution in a cross-platform multi-user environment.
Unfortunatly, that’s not an option, We don’t own that software, and getting them to buy it would be close to impossible. Usually I don’t have any trouble with Access, I can do just about anything I want, this just happens to be something that I’ve never tried before, so I’m trying to feel my way through it. Also, I don’t work in Access all the time. I’ll go months without doing any database work at all, then all of a sudden they’ll come back and give me a project that they need…I spend half the time remembering how I did things. I’ve just about got it…reading up on recordsets now, don’t know why it doesn’t like that code you gave me Derfel, but I’ll keep pluging till I get it.
Here’s a way to do it in Visual Basic…I’m not sure how you connect to your Access Database, but this is how I would do it:
Public Function CheckDate(EntryDate as Date) as ADODB.Recordset
dim con as Connection
dim strSQL as String
Dim rs as ADODB.Recordset
strSQL = "SELECT Count(*) AS Counter " & _
"FROM ReservationTable " & _
“WHERE ReservationTable.ReservationDate = '” & EntryDate & “’”
Set rs = New ADODB.Recordset
set con = new Connection
con.Provider = “Jet” <<—This might be wrong, I don’t know the constant for Access
…and connect to Access however you normally do it…
dim com as Command
set com = new Command
com.CommandType = adCmdText
com.commandText = strSQL
com.ActiveConnection = con
set CheckDate = com.Execute
com.ActiveConnection = Nothing
End Function
Public Sub FormCheck()
dim rs as Adodb.Recordset
set rs = CheckDate(TextboxThatContainsDate.Text)
if Rs!Counter > 0 Then
MsgBox "Reservation is in conflict!"
Else
UpdateFunction(values) <<--- or whatever you're going to do to update