Validation from multiple records

[jdlewin1]

Hi Cronkite es I'm sure it is the code and seems to work in the query format.

Galax.....how / where do I put that?

The form Reserve runs of the Reservations table where the data is held. The Assets field is a number field (Displayed as text when viewing) the other 2 fields are both date fields..

 

[Galaxiom]

Assuming Reservations.Asset is numeric:

& " WHERE Reservations.Asset=" & Forms!Reserve!Asset  _
& " AND Reservations.[Checked Out Date] <=" & Format(Forms!Reserve![Due Date] + 2, "\#mm\/dd\/yyyy\#")  _
& " AND Reservations.[Due Date] >=" &  Format(Forms!Reserve![Checked Out Date] - 2, "\#mm\/dd\/yyyy\#")

Your life will be easier if you stop using spaces in object names.

 

[jdlewin1]

hpw come there are & in the statement.....what do they do.

Do the date formats you put on the ends need to be in double or single quote marks?

I will try this this morning!!

 

[jdlewin1]

Galax...When you say:

Currentdb is the database. Its scope does not include the Application, where the Forms and their controls are located.

DO i need to add something else so it knows where the form and their controls are?

 

[jdlewin1]

hi Galax,

When i replace your code add my code with yours it changes the " to "" at the end of the first line?

 

[Galaxiom]

hpw come there are & in the statement.....what do they do.

The ampersand is the string concatenation operator. The literal strings are between the double quote marks. These are concatenated (joined together) with the values returned from the references to the controls on the form.

Do the date formats you put on the ends need to be in double or single quote marks?

Single quotes are used where they appear inside double quotes. In this case they are not inside the double quotes (literal strings in the sql command) but part of the Format function. They are the parameter that tells the Format function how to return the date.

In Access SQL all dates must be in the format #mm/dd/yyyy#. It can be achieved in several ways. The format string I used generates this structure and is universal for all regions. The back slashes are "literal escapes" meaning the subsequent character is to be inserted exactly as typed.

I have coloured the code to make this clearer. Literal parts are in Blue. Interpreted parts in Red.

& [COLOR="RoyalBlue"]" WHERE Reservations.Asset="[/COLOR] & [COLOR="Red"]Forms!Reserve!Asset[/COLOR]  _
& [COLOR="RoyalBlue"]" AND Reservations.[Checked Out Date] <="[/COLOR] &[COLOR="Red"] Format(Forms!Reserve![Due Date] + 2, "\#mm\/dd\/yyyy\#")[/COLOR]  _
& [COLOR="RoyalBlue"]" AND Reservations.[Due Date] >="[/COLOR] &  [COLOR="Red"]Format(Forms!Reserve![Checked Out Date] - 2, "\#mm\/dd\/yyyy\#")[/COLOR]

 

[Galaxiom]

hi Galax,

When i replace your code add my code with yours it changes the " to "" at the end of the first line?

That was a mistake in my original post which I have corrected.

 

[Galaxiom]

Galax...When you say:

Currentdb is the database. Its scope does not include the Application, where the Forms and their controls are located.

DO i need to add something else so it knows where the form and their controls are?

No. It is a one way thing. The Application knows about the database but not the reverse.

This is why DoCommand.RunSQL (its full title Application.DoCommand.RunSQL) can interpret Form references while CurrentDb.Execute and CurrentDb.OpenRecordset can't.

So with recordsets you need to learn about the complexities of concatenation and different delimiters required.

 

[jdlewin1]

Galax, you are a LEGEND !!! Works well now.

Now i just need it to set reset the [Check Out Date] to today and the [Due Date] to today +7 if the recordset empty, i have this up to now which i think works to this point, What do i need to do to reset the dates:

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)


If rs.RecordCount <> 0 Then
MsgBox ("Date Not Available - Equipment Already Booked Out. Please select another date or piece of equipment.")
Cancel = True
End If


ExitSub:
Exit Sub

ErrorHandler:
MsgBox "There has been an error. Please reload the form" * " Error:" & Error
Resume ExitSub

End Sub

 

[Galaxiom]

In VBA/Access, today is returned by the Date() function.

 Date() + 2

 

[jdlewin1]

Yes i am good with the Date() function but not sure on the code to get it to set the field in the form to Date() for [Checked Out Date] and Date()+7 for [Due Date] (which are their defaults)

effectively if the If rs.RecordCount <> 0 Then is true i want to cancel the operation clear / reset the dates to their default so the user has to enter new dates that are valid