Cross referencing tables

[mounty76]

Hello!

I have two tables (a & b). When I enter data in a form (two pieces of information are date and time), after I've entered the date and time in the form which updates table a I want it to check the records in table b for the same date and time, once this record is found there is a field called 'spaces' if this field is = 0 then I want a message box to appear saying "Course Full".......any ideas!! :banghead:

Thanks in advance!!!

 

[Minty]

You probably don't need to enter the initial information into a table until you have checked the dates are available.

I would probably use a DCount() to check if there are matching records in the target table. Something like this in the after update event of the the text box on the form

IF DCount("*","YourTargetTable,"[YourDateField] = " & Forms!YourForm!YourDateControl & " AND [YourTimeField] = " & Forms!YourForm!YourTimeControl & " AND [Spaces] = 0) > 0 Then
msgbox "Already Booked"
Else
msgbox "Available!"

End if

I suspect that this may not work immediately as I don't know what data types you have used to store your date and time fields. That information will help and may highlight a issue with your design.

Dates and times get tricky to handle if you aren't careful with your planning.

 

[mounty76]

Hi Minty,

Thanks for the heads up. I get a compile syntax error appear....this is your suggestion with my table names in (please don't shoot me down I know the names of tables and fields are awful but I was too far down the line to go back and change them all for something more suitable!!!)

IF DCount("*","Course Spaces Left, "[CStartD] = " & Forms!Data Entry Form!CStartD & " AND [Course Start Time] = " & Forms!Data Entry Form!Course Start Time & " AND [Spaces Left] = 0) > 0 Then
MsgBox "Already Booked"
Else
MsgBox "Available!"

Any suggestions most welcome!

Many Thanks

 

[Minty]

Because of the spaces you'll need to do some extra typing. I would strongly recommend changing them. A bit of pain now will save you mountains of grief and extra typing later trust me...

Try this;

IF DCount("*","[Course Spaces Left][COLOR="Red"]"[/COLOR], "[CStartD] = " & Forms![Data Entry Form]!CStartD & " AND [Course Start Time] = " & Forms![Data Entry Form]![Course Start Time] & " AND [Spaces Left] = 0) > 0 Then
         MsgBox "Already Booked"
Else
         MsgBox "Available!"
End If

As I said it would be handy to know what data types your course date and time fields are.

 

[mounty76]

Hi Minty,

Thanks for this, still getting syntax error:

IF DCount("*","[Course Spaces Left]", "[CStartD] = " & Forms![Data Entry Form]!CStartD & " AND [Course Start Time] = " & Forms![Data Entry Form]![Course Start Time] & " AND [Spaces Left] = 0) > 0 Then

I should have changed the names along ago, currently database is split with about 20 FE users with all different settings so would be a nightmare!

Cheers
Paul

 

[mounty76]

Also data types are long date and short time

 

[CJ_London]

you are missing a " at the end of your criteria which vba will be telling you with the syntax error (although with those spaces, the error description may be misleading). But hey, keep it as your nightmare


you are comparing datetime datatypes with text with this code

"[CStartD] = " & Forms![Data Entry Form]!CStartD & " AND [Course Start Time] = " & Forms![Data Entry Form]![Course Start Time]

To tell sql the text should be treated as a date you need to surround the string with #.

In addition, sql will treat the date string as US format of mm/dd/yyyy, except where the values are clearly dd/mm/yyyy - so 12/2/2018 will be treated as 2nd Dec, but 13/2/2018 will be treated as 13th Feb (there aren't 13 months in a year)

so your code needs to be

"[CStartD] = #" & format(Forms![Data Entry Form]!CStartD,"mm/dd/yyyy") & "# AND [Course Start Time] = #" & Forms![Data Entry Form]![Course Start Time] & "# AND [Spaces Left] = 0"

 

[mounty76]

Hi CJ,

Thanks for this, I'm getting a run time error with your code though?


If DCount("*", "[Course Spaces Left]", "[CStartD] = #" & Format(Forms![Data Entry Form]!CStartD, "dd/mm/yyyy") & "# AND [Course Start Time] = #" & Forms![Data Entry Form]![Course Start Time] & "# AND [Spaces Left] = 0") > 0 Then
MsgBox "Already Booked"
Else
MsgBox "Available!"
End If


Any ideas?

Many Thanks

 

[Minty]

I think you may need to post up a sample database - we're probably missing something obvious here that we can't see.

 

[CJ_London]

and it also helps to know what the runtime error actually is. So please also provide some example data - I suspect it is your start time on your form is not in a time format

 

[mounty76]

Hi Minty,

Thanks for this, here you go. I've stripped out as much as can.

Cheers

 

[CJ_London]

your spaces left field is a text field, either change it to a number type or change your code

....AND [Spaces Left] = '0'

 

[mounty76]

Hi CJ,

Thanks for this, it's now sort of working in that I don't get any syntax errors, however even when the spaces left value is 0 it still says there is space available? :banghead:

Cheers

 

[CJ_London]

according to your code that is to be expected

if...> 0 Then
MsgBox "Already Booked"
Else
MsgBox "Available!"

 

[mounty76]

Hi,

With the below code surely if the value is more than 0 it should return "Spaces are left on this course!" ? But it only ever returns "Course fully booked" no matter what the values are?

If DCount("*", "[Course Spaces Left]", "[CStartD] = #" & Format(Forms![Data Entry Form]!CStartD, "dd/mm/yyyy") & "# AND [Course Start Time] = #" & Forms![Data Entry Form]![Course Start Time] & "# AND [Spaces Left] = '0'") <= 0 Then
MsgBox "Course fully Booked"
Else
MsgBox "Spaces are left on this course!"
End If

 

[CJ_London]

dcount can never return a value less than 0

and I suspect your data is not what you think

time is stored as a decimal expressed as the time in seconds divided by the number of seconds in a day (86400) so 07:30 is 27000 seconds/86400=0.3125

remove all formatting in tables and controls and step through the code

For the record, you are storing a calculated value (spaces left) which is also not a good idea. It can be calculated based on deducting the count of the places booked from the max places allowed

 

[mounty76]

OK no problem, thanks for the help

 

[arnelgp]

Private Sub Course_Start_Time_AfterUpdate()
Dim var As Variant
var = DLookup("[Spaces Left]", "[Course Spaces Left]", "Format([CStartD] + Timevalue([Course Start Time]),'yyyymmddhhnn') = " & Format(Forms![Data Entry Form]!CStartD, "yyyymmdd") & Format(Forms![Data Entry Form]![Course Start Time], "hhnn"))
Debug.Print var
If IsNull(var) Then
    MsgBox "No course available for this date and time!"
Else
    If var = 0 Then
        MsgBox "Already Booked"
    Else
        MsgBox "Available!"
    End If
End If

End Sub

 

[mounty76]

Works perfectly first time! Thanks very much