error that closes Access driving me CRAZY!

J

JKlein

I have a form with many subforms. Upon updating a combo box these subforms
recordsources are set to a query that uses functions defined by text boxes
on the form. Occasionally I get an error that closes MS Access. If I
"comment out" the code in the combobox that resets the recordsource for the
subforms and place a command button with the "record sourcing" code I do not
get the error. Is there a difference in the way the code is executed?

Should it matter if the code is included in the combo box or the command
button?

Below is the code that is executed in the combox....gets errors. If I
comment it out in the combobox and execute it with a command button...no
error. Cant figure it out.

If I click the send error message to MS is there a way that MS can help with
the problem??

For cnt = FirstDay To ((DaysInMonth + FirstDay) - 1)
str_dayofmonth = fFormDate() + ((cnt - FirstDay))
Set frm = Forms!frm_CalendarNew.Controls("SF" & CStr(cnt)).Form
frm.RecordSource = "SELECT
MeetingId,MeetingDate,EmployeeFullName,ClientFullName,Starttime,Endtime FROM
tbl_TempCalendarData WHERE meetingdate = fdayofmonth()"
'Me.Controls("SF" & CStr(cnt)).Requery

If IsNull(cbo_ClientName) = False Then
frm.Controls("txb_name").ControlSource = "employeefullname"
End If
If IsNull(cbo_EmployeeName) = False Then
frm.Controls("txb_name").ControlSource = "clientfullname"
End If

frm.Controls("txb_time").ControlSource = "=Format([StartTime], 'h:mm
ampm') & ' - ' & Format([EndTime], 'h:mm ampm')"
Next
 
S

Scott McDaniel

I have a form with many subforms. Upon updating a combo box these subforms
recordsources are set to a query that uses functions defined by text boxes
on the form. Occasionally I get an error that closes MS Access. If I
"comment out" the code in the combobox that resets the recordsource for the
subforms and place a command button with the "record sourcing" code I do not
get the error. Is there a difference in the way the code is executed?

Should it matter if the code is included in the combo box or the command
button?
Click to expand...

No ... VBA makes no distinction between the two, however the values passed in could possibly be different depending on
when this occurs ... of course I don't think this is your situation.
Below is the code that is executed in the combox....gets errors. If I
comment it out in the combobox and execute it with a command button...no
error. Cant figure it out.

If I click the send error message to MS is there a way that MS can help with
the problem??
Click to expand...

No, not unless you pay for the service.

First: Why are resetting the .ControlSource for textboxes based on combo selections? Typically these are set once, and
not manipulated via code. Perhaps there's a better way of achieving your desired results. Of course, you may have a
valid reason for doing so.

Perhaps try to save the form's data before manipulating the Record and Control sources:

If Me.Dirty Then Me.Dirty = False
<now your other code>

In your For-Next loop, are you certain that your variables (i.e. FirstDay, DaysInMonth etc) actually contain values?

Also, try referencing the .Column property of your combos:

If IsNull(cbo_ClientName.Column(0))

I personally don't use IsNull in this fashion, but would instead try this:

If Len(Nz(Me.cbo_ClientName.Column(0)))=0
For cnt = FirstDay To ((DaysInMonth + FirstDay) - 1)
str_dayofmonth = fFormDate() + ((cnt - FirstDay))
Set frm = Forms!frm_CalendarNew.Controls("SF" & CStr(cnt)).Form
frm.RecordSource = "SELECT
MeetingId,MeetingDate,EmployeeFullName,ClientFullName,Starttime,Endtime FROM
tbl_TempCalendarData WHERE meetingdate = fdayofmonth()"
'Me.Controls("SF" & CStr(cnt)).Requery

If IsNull(cbo_ClientName) = False Then
frm.Controls("txb_name").ControlSource = "employeefullname"
End If
If IsNull(cbo_EmployeeName) = False Then
frm.Controls("txb_name").ControlSource = "clientfullname"
End If

frm.Controls("txb_time").ControlSource = "=Format([StartTime], 'h:mm
ampm') & ' - ' & Format([EndTime], 'h:mm ampm')"
Next
Click to expand...

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com
 
Top