Do / Loop ignores code unless msgbox

[Widemonk]

I have a small table and associated form with just 1 field that contains the
8 bank (public) holidays throughout the year (each as a separate record) and
I want to compare all these records against another field in a form where
people enter their holiday (vacation) dates.

The following code only works with the message box. If i delete the message
box line or even move it to within the IF function, it seems the IF function
doesnt execute.

____________

DoCmd.OpenForm "BankHols", acNormal, , , acFormReadOnly
DoCmd.GoToRecord , , acFirst

Do

MsgBox "Test messagebox"

If [Forms]![formholidays]![txtStartHol] =
[Forms]![bankhols]![txtBankHolidayDate] Then
MsgBox "Holiday Cannot start on a bank holiday"
DoCmd.Close acForm, "bankhols", acSaveNo
Exit Sub
End If

DoCmd.RunCommand (acCmdRecordsGoToNext)
Loop Until ...... [all 8 records checked]

DoCmd.Close acForm, "bankhols", acSaveNo

____________

Any ideas please ??

 

[Amy Blankenship]

One possibility is to just open the associated recordset instead of the form
and loop through it that way.

It looks like the form isn't completely open when you try to run your loop.
If you want to do it this way, you should have the CALLED form run the code
on load, or loop until you can see the value of the control in question on
it.

HTH;

Amy

 

[Stefan Hoffmann]

hi,

The following code only works with the message box. If i delete the message
box line or even move it to within the IF function, it seems the IF function
doesnt execute.

Sounds like broken compiled code. Try the /decompile option:
C:\omplete Path\to\MSACCESS.EXE /decompile C:\omplete Path\to.mdb

Any ideas please ??

If DCount("txtBankHolidayDate", "bankhols", "BankHolidayDate = " & _
[Forms]![formholidays]![txtStartHol]) <> 0 Then
' Match
Else
' No Match
End If


mfG
--> stefan <--

 

[Widemonk]

Open the recordset with 'DoCmd.OpenTable' ?? I tried that but just got the
'access couldnt find the field '|' referred to in your expression'...
--------
If [Forms]![formholidays]![txtNewStart] =
[tables]![tablebankholidays]![BankHoliday] Then
--------

One possibility is to just open the associated recordset instead of the form
and loop through it that way.

It looks like the form isn't completely open when you try to run your loop.
If you want to do it this way, you should have the CALLED form run the code
on load, or loop until you can see the value of the control in question on
it.

HTH;

Amy

I have a small table and associated form with just 1 field that contains
the
8 bank (public) holidays throughout the year (each as a separate record)
and
I want to compare all these records against another field in a form where
people enter their holiday (vacation) dates.

The following code only works with the message box. If i delete the
message
box line or even move it to within the IF function, it seems the IF
function
doesnt execute.

____________

DoCmd.OpenForm "BankHols", acNormal, , , acFormReadOnly
DoCmd.GoToRecord , , acFirst

Do

MsgBox "Test messagebox"

If [Forms]![formholidays]![txtStartHol] =
[Forms]![bankhols]![txtBankHolidayDate] Then
MsgBox "Holiday Cannot start on a bank holiday"
DoCmd.Close acForm, "bankhols", acSaveNo
Exit Sub
End If

DoCmd.RunCommand (acCmdRecordsGoToNext)
Loop Until ...... [all 8 records checked]

DoCmd.Close acForm, "bankhols", acSaveNo

____________

Any ideas please ??

 

[Amy Blankenship]

Open the recordset with 'DoCmd.OpenTable' ?? I tried that but just got the
'access couldnt find the field '|' referred to in your expression'...

No, something more like

dim db as DAO.database, rs as DAO.recordset

set db = currentDB
set rs = db.OpenRecordset("SELECT BankHoliday FROM tablebankholidays WHERE
BankHoliday = #" & Forms!formholidays!txtNewStart & "#"

If Not rs.EOF then
'there's a bank holiday that's the same as the selected date
MsgBox "Holiday cannot start on a bank holiday."
End if

rs.close
set rs = nothing
set db = nothing

HTH;

Amy