Cross referencing tables (1 Viewer)

mounty76 · Feb 14, 2018

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 · Feb 14, 2018

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

Code:

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 · Feb 14, 2018

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 · Feb 14, 2018

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;

Code:

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 · Feb 14, 2018

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 · Feb 14, 2018

Also data types are long date and short time

CJ_London · Feb 14, 2018

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 · Feb 15, 2018

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 · Feb 15, 2018

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 · Feb 15, 2018

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 · Feb 15, 2018

Hi Minty,

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

Cheers

CJ_London · Feb 15, 2018

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

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

mounty76 · Feb 15, 2018

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 · Feb 15, 2018

according to your code that is to be expected

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

mounty76 · Feb 15, 2018

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 · Feb 15, 2018

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 · Feb 15, 2018

OK no problem, thanks for the help

arnelgp · Feb 15, 2018

Code:

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 · Feb 15, 2018

Works perfectly first time! Thanks very much

Cross referencing tables (1 Viewer)

Registered User.

AWF VIP

Registered User.

AWF VIP

Registered User.

Registered User.

Super Moderator

Registered User.

AWF VIP

Super Moderator

Registered User.

Attachments

Super Moderator

Registered User.

Super Moderator

Registered User.

Super Moderator

Registered User.

..forever waiting... waiting for jellybean!

Registered User.

Similar threads

Users who are viewing this thread