me.requery

  • Thread starter Thread starter Claire
  • Start date Start date
C

Claire

Hi everyone,
I'm just finishing up my first form that includes 4 or 5 tables, has
multiple filters, check boxes that perform functions, and labels that become
inabled and have three different enabled states. I added each function
individually, and had no problems until my final testing, and now I'm at a
loss of where I may be wrong.

What's going on: The form has a two cascading combo boxes. When you select
the customer number in the first combo box (CustNo), it gives you the
possibilities for site number (SiteNo). Right now, these are both blank when
you open the form, and there are no records(devices). This is a continuous
form. Selecting the CustNo works fine, as does SiteNo, and when these are
selected, the after update event accurately pulls up the correct devices.
All of the functions and on click events work fine from here.

However, if I select a different customer number and site number, despite
the fact that there is a me.requery in the after update event, the
records/devices do not change. It keeps the devices from the first site
looked at. It looks like all of the other boxes and labels are updating and
displaying for the records that SHOULD be in the form (they're looked at as a
recordset). Does anyone have any thoughts to what's going on?

Below is the SQL for the query that's the data source for the form, and the
after update code for SiteNo and CustNo. Those seem like the most applicable
code.

Thanks so much for helping with this one! I'm out of hypotheses to check!


~Claire

SQL:
SELECT Devices.CustNo, Devices.SiteNo, Devices.DeviceNo, Devices.DeviceType,
Devices.UniqueDescription, Devices.Floor, Devices.FACPZoneNo,
fConcatChild("DeviceZoning","DeviceNo","ZoneNo","Long",[DeviceNo]) AS
ZonedTo, Devices.InactiveDate
FROM Devices
WHERE (((Devices.CustNo)=[forms]![frmZoneDevices4].[CustNo]) AND
((Devices.SiteNo)=[forms]![frmZoneDevices4].[SiteNo]) AND
((Devices.InactiveDate) Is Null));



Private Sub CustNo_AfterUpdate()
fnClearSiteTextBoxes
fnResetZoneLabels
fnBlankZones
Me.SiteNo = Null

If IsNull(CustNo) Then
Me.SiteNo.Enabled = False
Else
Me.SiteNo.Enabled = True
Me.SiteNo.RowSource = ReplaceWhereClause(Me.SiteNo.RowSource, _
"Where CustNo = """ & Me.CustNo & """")
Me.SiteNo.Requery
End If
Me.Requery
End Sub




Private Sub SiteNo_AfterUpdate()
'Update Site Name and Address
Me.SiteName = ELookup("SiteName", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")
Me.SiteAddress = ELookup("SiteAddress", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")
Me.SiteCity = ELookup("SiteCity", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")

'clear out filters
fnClrFilterEntry
fnClearZoning
txtFilter = ""
Me.FilterOn = False

'Update Zone Info
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intZoneCount As Integer 'how many zones the site has
Dim IntZoneMo As Integer 'the month of the last zone listed
Dim IntZoneInt As Integer 'the number of the zone currently using (1st,
2nd, 3rd, etc)
Dim StrCurrZoneNo As String 'current ZoneMo field name
Dim StrCurrZoneMo As String 'Current ZoneNo field name
Dim strCurrZoneDesc As String 'Current ZoneDesc field name

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT ZoneNo, ZoneMonth, Description FROM
Zones WHERE " & _
"CustNo= """ & Me.CustNo & """ AND SiteNo= """ & Me.SiteNo & """ AND
InactiveDate is null " & _
"ORDER BY ZoneMonth", dbOpenDynaset)

'Count the number of zones for the site
If rst.AbsolutePosition > -1 Then
rst.MoveLast
intZoneCount = rst.RecordCount
rst.MoveFirst


IntZoneMo = 0
IntZoneInt = 0

'Fill in the zone numbers, months, and descriptions for each
possible zone
Do While intZoneCount > IntZoneInt
IntZoneInt = IntZoneInt + 1

StrCurrZoneNo = "ZoneNo" & IntZoneInt
StrCurrZoneMo = "ZoneMo" & IntZoneInt
strCurrZoneDesc = "ZoneDesc" & IntZoneInt

Me.Controls(StrCurrZoneNo) = rst!ZoneNo
Me.Controls(StrCurrZoneMo) = rst!ZoneMonth
Me.Controls(strCurrZoneDesc) = rst!Description

If Not rst.EOF Then
rst.MoveNext
Else: MsgBox ("You Shouldn't get here")
End If
Loop
End If

DoCmd.Requery
fnZoneButtons 'Make the zones raised, sunken, or chiseled depending on
the records
fnBlankZones 'make text boxes, labels, combo boxes, visible or invisible
if there is no zone
End Sub
 
Try this solution

The problem is that on a continuous Form, there is really only *one* combo
box - displayed many times. If you change its properties (its RowSource in
this case), all rows reflect that change.

One somewhat snarky but effective getaround is to put a Textbox onto the
form, carefully superimposed over the text area of the combo box (don't
cover the dropdown tool though). Set its Control Source to either a DLookUp
expression looking up the current record's value from the lookup table, or
(if practical) base the Form on a query joined to the lookup table and
simply make it a bound textbox.

Set the textbox's Enabled = No, Locked = Yes, Tab Stop = No so the user
can't do anything with it; it's for display only. You may need to move it in
front of the combo box (with Move To Front on the Format menu).

When the user selects the dropdown, the combo box data will come in front
and allow (filtered) selection; when it's not selected, the user will see
the textbox.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Claire said:
Hi everyone,
I'm just finishing up my first form that includes 4 or 5 tables, has
multiple filters, check boxes that perform functions, and labels that
become
inabled and have three different enabled states. I added each function
individually, and had no problems until my final testing, and now I'm at a
loss of where I may be wrong.

What's going on: The form has a two cascading combo boxes. When you
select
the customer number in the first combo box (CustNo), it gives you the
possibilities for site number (SiteNo). Right now, these are both blank
when
you open the form, and there are no records(devices). This is a
continuous
form. Selecting the CustNo works fine, as does SiteNo, and when these are
selected, the after update event accurately pulls up the correct devices.
All of the functions and on click events work fine from here.

However, if I select a different customer number and site number, despite
the fact that there is a me.requery in the after update event, the
records/devices do not change. It keeps the devices from the first site
looked at. It looks like all of the other boxes and labels are updating
and
displaying for the records that SHOULD be in the form (they're looked at
as a
recordset). Does anyone have any thoughts to what's going on?

Below is the SQL for the query that's the data source for the form, and
the
after update code for SiteNo and CustNo. Those seem like the most
applicable
code.

Thanks so much for helping with this one! I'm out of hypotheses to check!


~Claire

SQL:
SELECT Devices.CustNo, Devices.SiteNo, Devices.DeviceNo,
Devices.DeviceType,
Devices.UniqueDescription, Devices.Floor, Devices.FACPZoneNo,
fConcatChild("DeviceZoning","DeviceNo","ZoneNo","Long",[DeviceNo]) AS
ZonedTo, Devices.InactiveDate
FROM Devices
WHERE (((Devices.CustNo)=[forms]![frmZoneDevices4].[CustNo]) AND
((Devices.SiteNo)=[forms]![frmZoneDevices4].[SiteNo]) AND
((Devices.InactiveDate) Is Null));



Private Sub CustNo_AfterUpdate()
fnClearSiteTextBoxes
fnResetZoneLabels
fnBlankZones
Me.SiteNo = Null

If IsNull(CustNo) Then
Me.SiteNo.Enabled = False
Else
Me.SiteNo.Enabled = True
Me.SiteNo.RowSource = ReplaceWhereClause(Me.SiteNo.RowSource, _
"Where CustNo = """ & Me.CustNo & """")
Me.SiteNo.Requery
End If
Me.Requery
End Sub




Private Sub SiteNo_AfterUpdate()
'Update Site Name and Address
Me.SiteName = ELookup("SiteName", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")
Me.SiteAddress = ELookup("SiteAddress", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")
Me.SiteCity = ELookup("SiteCity", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")

'clear out filters
fnClrFilterEntry
fnClearZoning
txtFilter = ""
Me.FilterOn = False

'Update Zone Info
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intZoneCount As Integer 'how many zones the site has
Dim IntZoneMo As Integer 'the month of the last zone listed
Dim IntZoneInt As Integer 'the number of the zone currently using
(1st,
2nd, 3rd, etc)
Dim StrCurrZoneNo As String 'current ZoneMo field name
Dim StrCurrZoneMo As String 'Current ZoneNo field name
Dim strCurrZoneDesc As String 'Current ZoneDesc field name

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT ZoneNo, ZoneMonth, Description FROM
Zones WHERE " & _
"CustNo= """ & Me.CustNo & """ AND SiteNo= """ & Me.SiteNo & """
AND
InactiveDate is null " & _
"ORDER BY ZoneMonth", dbOpenDynaset)

'Count the number of zones for the site
If rst.AbsolutePosition > -1 Then
rst.MoveLast
intZoneCount = rst.RecordCount
rst.MoveFirst


IntZoneMo = 0
IntZoneInt = 0

'Fill in the zone numbers, months, and descriptions for each
possible zone
Do While intZoneCount > IntZoneInt
IntZoneInt = IntZoneInt + 1

StrCurrZoneNo = "ZoneNo" & IntZoneInt
StrCurrZoneMo = "ZoneMo" & IntZoneInt
strCurrZoneDesc = "ZoneDesc" & IntZoneInt

Me.Controls(StrCurrZoneNo) = rst!ZoneNo
Me.Controls(StrCurrZoneMo) = rst!ZoneMonth
Me.Controls(strCurrZoneDesc) = rst!Description

If Not rst.EOF Then
rst.MoveNext
Else: MsgBox ("You Shouldn't get here")
End If
Loop
End If

DoCmd.Requery
fnZoneButtons 'Make the zones raised, sunken, or chiseled depending on
the records
fnBlankZones 'make text boxes, labels, combo boxes, visible or
invisible
if there is no zone
End Sub
Click to expand...
 
Thank you for the response, but it seems like I missed adding a key element-
all of the text boxes and combo boxes are part of the form header, so sadly
this suggestion doesn't apply. Does anyone have any other thoughts?

Thanks!
Claire

Jeanette Cunningham said:
Try this solution

The problem is that on a continuous Form, there is really only *one* combo
box - displayed many times. If you change its properties (its RowSource in
this case), all rows reflect that change.

One somewhat snarky but effective getaround is to put a Textbox onto the
form, carefully superimposed over the text area of the combo box (don't
cover the dropdown tool though). Set its Control Source to either a DLookUp
expression looking up the current record's value from the lookup table, or
(if practical) base the Form on a query joined to the lookup table and
simply make it a bound textbox.

Set the textbox's Enabled = No, Locked = Yes, Tab Stop = No so the user
can't do anything with it; it's for display only. You may need to move it in
front of the combo box (with Move To Front on the Format menu).

When the user selects the dropdown, the combo box data will come in front
and allow (filtered) selection; when it's not selected, the user will see
the textbox.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Claire said:
Hi everyone,
I'm just finishing up my first form that includes 4 or 5 tables, has
multiple filters, check boxes that perform functions, and labels that
become
inabled and have three different enabled states. I added each function
individually, and had no problems until my final testing, and now I'm at a
loss of where I may be wrong.

What's going on: The form has a two cascading combo boxes. When you
select
the customer number in the first combo box (CustNo), it gives you the
possibilities for site number (SiteNo). Right now, these are both blank
when
you open the form, and there are no records(devices). This is a
continuous
form. Selecting the CustNo works fine, as does SiteNo, and when these are
selected, the after update event accurately pulls up the correct devices.
All of the functions and on click events work fine from here.

However, if I select a different customer number and site number, despite
the fact that there is a me.requery in the after update event, the
records/devices do not change. It keeps the devices from the first site
looked at. It looks like all of the other boxes and labels are updating
and
displaying for the records that SHOULD be in the form (they're looked at
as a
recordset). Does anyone have any thoughts to what's going on?

Below is the SQL for the query that's the data source for the form, and
the
after update code for SiteNo and CustNo. Those seem like the most
applicable
code.

Thanks so much for helping with this one! I'm out of hypotheses to check!


~Claire

SQL:
SELECT Devices.CustNo, Devices.SiteNo, Devices.DeviceNo,
Devices.DeviceType,
Devices.UniqueDescription, Devices.Floor, Devices.FACPZoneNo,
fConcatChild("DeviceZoning","DeviceNo","ZoneNo","Long",[DeviceNo]) AS
ZonedTo, Devices.InactiveDate
FROM Devices
WHERE (((Devices.CustNo)=[forms]![frmZoneDevices4].[CustNo]) AND
((Devices.SiteNo)=[forms]![frmZoneDevices4].[SiteNo]) AND
((Devices.InactiveDate) Is Null));



Private Sub CustNo_AfterUpdate()
fnClearSiteTextBoxes
fnResetZoneLabels
fnBlankZones
Me.SiteNo = Null

If IsNull(CustNo) Then
Me.SiteNo.Enabled = False
Else
Me.SiteNo.Enabled = True
Me.SiteNo.RowSource = ReplaceWhereClause(Me.SiteNo.RowSource, _
"Where CustNo = """ & Me.CustNo & """")
Me.SiteNo.Requery
End If
Me.Requery
End Sub




Private Sub SiteNo_AfterUpdate()
'Update Site Name and Address
Me.SiteName = ELookup("SiteName", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")
Me.SiteAddress = ELookup("SiteAddress", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")
Me.SiteCity = ELookup("SiteCity", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")

'clear out filters
fnClrFilterEntry
fnClearZoning
txtFilter = ""
Me.FilterOn = False

'Update Zone Info
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intZoneCount As Integer 'how many zones the site has
Dim IntZoneMo As Integer 'the month of the last zone listed
Dim IntZoneInt As Integer 'the number of the zone currently using
(1st,
2nd, 3rd, etc)
Dim StrCurrZoneNo As String 'current ZoneMo field name
Dim StrCurrZoneMo As String 'Current ZoneNo field name
Dim strCurrZoneDesc As String 'Current ZoneDesc field name

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT ZoneNo, ZoneMonth, Description FROM
Zones WHERE " & _
"CustNo= """ & Me.CustNo & """ AND SiteNo= """ & Me.SiteNo & """
AND
InactiveDate is null " & _
"ORDER BY ZoneMonth", dbOpenDynaset)

'Count the number of zones for the site
If rst.AbsolutePosition > -1 Then
rst.MoveLast
intZoneCount = rst.RecordCount
rst.MoveFirst


IntZoneMo = 0
IntZoneInt = 0

'Fill in the zone numbers, months, and descriptions for each
possible zone
Do While intZoneCount > IntZoneInt
IntZoneInt = IntZoneInt + 1

StrCurrZoneNo = "ZoneNo" & IntZoneInt
StrCurrZoneMo = "ZoneMo" & IntZoneInt
strCurrZoneDesc = "ZoneDesc" & IntZoneInt

Me.Controls(StrCurrZoneNo) = rst!ZoneNo
Me.Controls(StrCurrZoneMo) = rst!ZoneMonth
Me.Controls(strCurrZoneDesc) = rst!Description

If Not rst.EOF Then
rst.MoveNext
Else: MsgBox ("You Shouldn't get here")
End If
Loop
End If

DoCmd.Requery
fnZoneButtons 'Make the zones raised, sunken, or chiseled depending on
the records
fnBlankZones 'make text boxes, labels, combo boxes, visible or
invisible
if there is no zone
End Sub
Click to expand...


.
Click to expand...
 
Update: I've fixed it. Amazingly enough my brain decided to fire some new
neurons, and I figured it out. I have a private string variable in the form
that stored the filter, and though I set it to "" in the SiteNo update event,
I had not touched it in the CustNo update. Hooray for new ideas!

~Claire

Claire said:
Hi everyone,
I'm just finishing up my first form that includes 4 or 5 tables, has
multiple filters, check boxes that perform functions, and labels that become
inabled and have three different enabled states. I added each function
individually, and had no problems until my final testing, and now I'm at a
loss of where I may be wrong.

What's going on: The form has a two cascading combo boxes. When you select
the customer number in the first combo box (CustNo), it gives you the
possibilities for site number (SiteNo). Right now, these are both blank when
you open the form, and there are no records(devices). This is a continuous
form. Selecting the CustNo works fine, as does SiteNo, and when these are
selected, the after update event accurately pulls up the correct devices.
All of the functions and on click events work fine from here.

However, if I select a different customer number and site number, despite
the fact that there is a me.requery in the after update event, the
records/devices do not change. It keeps the devices from the first site
looked at. It looks like all of the other boxes and labels are updating and
displaying for the records that SHOULD be in the form (they're looked at as a
recordset). Does anyone have any thoughts to what's going on?

Below is the SQL for the query that's the data source for the form, and the
after update code for SiteNo and CustNo. Those seem like the most applicable
code.

Thanks so much for helping with this one! I'm out of hypotheses to check!


~Claire

SQL:
SELECT Devices.CustNo, Devices.SiteNo, Devices.DeviceNo, Devices.DeviceType,
Devices.UniqueDescription, Devices.Floor, Devices.FACPZoneNo,
fConcatChild("DeviceZoning","DeviceNo","ZoneNo","Long",[DeviceNo]) AS
ZonedTo, Devices.InactiveDate
FROM Devices
WHERE (((Devices.CustNo)=[forms]![frmZoneDevices4].[CustNo]) AND
((Devices.SiteNo)=[forms]![frmZoneDevices4].[SiteNo]) AND
((Devices.InactiveDate) Is Null));



Private Sub CustNo_AfterUpdate()
fnClearSiteTextBoxes
fnResetZoneLabels
fnBlankZones
Me.SiteNo = Null

If IsNull(CustNo) Then
Me.SiteNo.Enabled = False
Else
Me.SiteNo.Enabled = True
Me.SiteNo.RowSource = ReplaceWhereClause(Me.SiteNo.RowSource, _
"Where CustNo = """ & Me.CustNo & """")
Me.SiteNo.Requery
End If
Me.Requery
End Sub




Private Sub SiteNo_AfterUpdate()
'Update Site Name and Address
Me.SiteName = ELookup("SiteName", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")
Me.SiteAddress = ELookup("SiteAddress", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")
Me.SiteCity = ELookup("SiteCity", "Sites", "CustNo = """ & _
Me.CustNo & """ AND SiteNo = """ & _
Me.SiteNo & """")

'clear out filters
fnClrFilterEntry
fnClearZoning
txtFilter = ""
Me.FilterOn = False

'Update Zone Info
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intZoneCount As Integer 'how many zones the site has
Dim IntZoneMo As Integer 'the month of the last zone listed
Dim IntZoneInt As Integer 'the number of the zone currently using (1st,
2nd, 3rd, etc)
Dim StrCurrZoneNo As String 'current ZoneMo field name
Dim StrCurrZoneMo As String 'Current ZoneNo field name
Dim strCurrZoneDesc As String 'Current ZoneDesc field name

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT ZoneNo, ZoneMonth, Description FROM
Zones WHERE " & _
"CustNo= """ & Me.CustNo & """ AND SiteNo= """ & Me.SiteNo & """ AND
InactiveDate is null " & _
"ORDER BY ZoneMonth", dbOpenDynaset)

'Count the number of zones for the site
If rst.AbsolutePosition > -1 Then
rst.MoveLast
intZoneCount = rst.RecordCount
rst.MoveFirst


IntZoneMo = 0
IntZoneInt = 0

'Fill in the zone numbers, months, and descriptions for each
possible zone
Do While intZoneCount > IntZoneInt
IntZoneInt = IntZoneInt + 1

StrCurrZoneNo = "ZoneNo" & IntZoneInt
StrCurrZoneMo = "ZoneMo" & IntZoneInt
strCurrZoneDesc = "ZoneDesc" & IntZoneInt

Me.Controls(StrCurrZoneNo) = rst!ZoneNo
Me.Controls(StrCurrZoneMo) = rst!ZoneMonth
Me.Controls(strCurrZoneDesc) = rst!Description

If Not rst.EOF Then
rst.MoveNext
Else: MsgBox ("You Shouldn't get here")
End If
Loop
End If

DoCmd.Requery
fnZoneButtons 'Make the zones raised, sunken, or chiseled depending on
the records
fnBlankZones 'make text boxes, labels, combo boxes, visible or invisible
if there is no zone
End Sub
Click to expand...
 
Back
Top