# Access and Me...Can you help Resolve our Conflict? **URL:** \ **Category:** Factual Questions\ **Created:** [July 5, 2000, 3:09pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646 "2000-07-05T15:09:30Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Atrael](https://avatars.discourse-cdn.com/v4/letter/a/6a8cbe/32.png) [@Atrael](https://boards.straightdope.com/u/Atrael)\ **Post date:** [July 5, 2000, 3:09pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/1 "2000-07-05T15:09:30Z")
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. Thanks again… Atrael. ---
**Author:** ![Lord\_Derfel](https://avatars.discourse-cdn.com/v4/letter/l/bb73d2/32.png) [@Lord\_Derfel](https://boards.straightdope.com/u/Lord_Derfel)\ **Post date:** [July 5, 2000, 5:53pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/2 "2000-07-05T17:53:31Z")
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…) ---
**Author:** ![Atrael](https://avatars.discourse-cdn.com/v4/letter/a/6a8cbe/32.png) [@Atrael](https://boards.straightdope.com/u/Atrael)\ **Post date:** [July 5, 2000, 6:31pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/3 "2000-07-05T18:31:29Z")
Lord Derfel, 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. ---
**Author:** ![hardcore](https://avatars.discourse-cdn.com/v4/letter/h/b5a626/32.png) [@hardcore](https://boards.straightdope.com/u/hardcore)\ **Post date:** [July 5, 2000, 7:24pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/4 "2000-07-05T19:24:45Z")
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. ---
**Author:** ![Atrael](https://avatars.discourse-cdn.com/v4/letter/a/6a8cbe/32.png) [@Atrael](https://boards.straightdope.com/u/Atrael)\ **Post date:** [July 5, 2000, 7:38pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/5 "2000-07-05T19:38:22Z")
Hardcore, 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. ---
**Author:** ![hardcore](https://avatars.discourse-cdn.com/v4/letter/h/b5a626/32.png) [@hardcore](https://boards.straightdope.com/u/hardcore)\ **Post date:** [July 5, 2000, 8:27pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/6 "2000-07-05T20:27:33Z")
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. ---
**Author:** ![Lord\_Derfel](https://avatars.discourse-cdn.com/v4/letter/l/bb73d2/32.png) [@Lord\_Derfel](https://boards.straightdope.com/u/Lord_Derfel)\ **Post date:** [July 5, 2000, 8:48pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/7 "2000-07-05T20:48:42Z")
Using the query I wrote above, here’s some code to execute it - put it in the BeforeUpdate or AfterUpdate event of your control: ```auto 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. ---
**Author:** ![Atrael](https://avatars.discourse-cdn.com/v4/letter/a/6a8cbe/32.png) [@Atrael](https://boards.straightdope.com/u/Atrael)\ **Post date:** [July 6, 2000, 3:44pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/8 "2000-07-06T15:44:43Z")
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. ---
**Author:** ![AHunter3](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/ahunter3/32/368_2.png) [@AHunter3](https://boards.straightdope.com/u/AHunter3)\ **Post date:** [July 6, 2000, 3:49pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/9 "2000-07-06T15:49:20Z")
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. ---
**Author:** ![Atrael](https://avatars.discourse-cdn.com/v4/letter/a/6a8cbe/32.png) [@Atrael](https://boards.straightdope.com/u/Atrael)\ **Post date:** [July 6, 2000, 4:34pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/10 "2000-07-06T16:34:14Z")
AHunter3, 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. ---
**Author:** ![Starbury](https://avatars.discourse-cdn.com/v4/letter/s/f04885/32.png) [@Starbury](https://boards.straightdope.com/u/Starbury)\ **Post date:** [July 6, 2000, 6:04pm UTC](https://boards.straightdope.com/t/access-and-me-can-you-help-resolve-our-conflict/24646/11 "2000-07-06T18:04:19Z")
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 ``` end if set rs = nothing End Sub