ARRRGGGHHH!! Syntax problem with requery on a form other than curr

[DawnTreader]

Hello All

i must be totally dumb. i have been coding for a good 2 years on this app
and yet for some reason i cant get this to work.

Private Sub btnCloseSite_Click()
On Error GoTo Err_btnCloseSites_Click

Dim meopenArgs As String

If Me.cbServiceRepID = 1 Or IsNull(Me.cbServiceRepID) Then
MsgBox "Please choose a Service Representative, if in doubt choose
The Default.", vbOKOnly, "Service Representative Needed"
GoTo Exit_btnCloseSites_Click
End If

If Me.Dirty = True Then
If IsNull(Me.cbCustomerListID) Then
Me.cbCustomerListID.Value = Me.CustomerListID
End If
' DoCmd.RunCommand acCmdSaveRecord

'check to see if we are adding a site from a particular button
If Not IsNull(Me.OpenArgs) Then
meopenArgs = Me.Form.OpenArgs
Select Case meopenArgs
Case "ChangeSite"
Forms!pfrmChangeProductSite!cbChangeSiteList.Requery
Forms!pfrmChangeProductSite!cbChangeSiteList.SetFocus

Forms!zzMAINFORM.sfrmproductstositeinformation.Form.Requery
MsgBox "change site accomplished", vbOKOnly, "thingy"
Case "VisualPartsLink"
Forms!frmVisualPartsLink.Form.SetFocus
Forms!frmVisualPartsLink!cboSite.Value = 1
Forms!frmVisualPartsLink!cboSite.Requery
Forms!frmVisualPartsLink!cboSite.SetFocus
Forms!frmVisualPartsLink!cboSite.Value = Me.SiteID
MsgBox "visual part link accomplished", vbOKOnly, "thingy"
Case "ViewCustomerSiteDetails"
Forms!zzMAINFORM!SerialNumber.SetFocus
Forms!zzMAINFORM!sfrmproductstositeinformation.Requery
MsgBox "view customer site details accomplished",
vbOKOnly, "thingy"
Case "ViewAllSites"
Forms!zzMAINFORM!SerialNumber.SetFocus
Forms!zzMAINFORM!sfrmproductstositeinformation.Requery
MsgBox "view all sites accomplished", vbOKOnly, "thingy"
Case Else
Forms!zzMAINFORM!SerialNumber.SetFocus
Forms!zzMAINFORM!sfrmproductstositeinformation.Requery
MsgBox "had to do an else", vbOKOnly, "thingy"
End Select
End If
End If

DoCmd.Close acForm, "zySiteInformation"

Exit_btnCloseSites_Click:
Exit Sub

Err_btnCloseSites_Click:
MsgBox Err.Description
Resume Exit_btnCloseSites_Click
End Sub

everytime i test it i am getting no results, my combo box is not being
refreshed. i dont understand why. i have researched the syntax and have
changed it a few times to try and get the combo on the pfrmChangeProductSite
to requery. it doesnt even take the focus. do i need to do something
different? do i need to focus on the form first? this is so annoying because
from what i remember it was working at one time. this is on my close button,
the code that did work was on the close event of the form that this code is
on.

i know the case is working because i get the message box poping up. i have
even used breakpoints and watched it walk through the code. i am going to set
up a debug.print to see the value of the combo box, but at this point i
figured maybe someone would see something in this code i dont.

i am going to play around with it, move the code back to the close event of
the form, but i could really use any help and suggestions.

 

[DawnTreader]

Hello Again

here is another related question. the user has typed in a combo box on a
form before the one the code i posted is on. how do i take the value they
typed in, which doesnt exist, and put it in a text box on the form that
creates the record for the values in the combo box.

for example on the form pfrmchangesite the combo box gets typed in and the
value the user types in is "not in list" so it opens the form
frmSiteInformation. i would like it to pass the value the user typed in the
combo box as the name of the site that the user will create in the
frmSiteInformation. i dont know if this makes sense, but here is the code
that i am trying to use:

Private Sub cbChangeSiteList_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cbChangeSiteList_NotInList

If MsgBox("Add New Site?", vbYesNo, "Add New Site?") = vbYes Then
DoCmd.OpenForm "frmSiteInformation", acNormal, , , acFormAdd, ,
"ChangeSite"
Response = acDataErrContinue
Forms!frmSiteInformation!SiteCommonName =
Me.cbChangeSiteList.Column(1) ' this is where i hope to capture what was typed
Me.cbChangeSiteList.Value = ""
Else
MsgBox "The Site entered is not in the application. Please add it or
choose from list.", vbOKOnly, "Not In List"
Response = acDataErrContinue
End If

Exit_cbChangeSiteList_NotInList:
Exit Sub

Err_cbChangeSiteList_NotInList:
MsgBox Err.Number & " " & Err.Description
Resume Exit_cbChangeSiteList_NotInList

End Sub

 

[BruceM]

NewData is the value typed into a combo box that has Limit to List set to
Yes. This link provides some specifics about adding an item that isn't on
the list:
http://www.mvps.org/access/forms/frm0015.htm

Regarding your first posting, maybe somebody will be able to decipher what
you are trying to do, but I find it quite bewildering. It may help if you
describe in real-world terms what you hope to accomplish. For specifics,
there seems to be at least four forms that are updated or requeried or
something, along with some combo boxes that are requeried, but for what
purpose is hard to tell since they do not seem to have a new Row Source.
Are the forms open? If you are trying to send the user to another form, why
not simply open the other form? If the other form is already open, why are
you requerying it? Why have you commented out the code to save the record?
Why are you adding two values, one after the other, to the combo box
(Forms!frmVisualPartsLink!cboSite.Value = 1, then
Forms!frmVisualPartsLink!cboSite.Value = Me.SiteID)?

 

[DawnTreader]

Hello All

well i fiddled and researched, i am so blind sometimes.

' DoCmd.RunCommand acCmdSaveRecord

all i needed was to remove the '

the record needs to be saved before refreshing the combo boxes.

additionally the other question i posted is solved by using the NewData
variable in the subroutine opening line as the field value on the opening
form. for example:

Forms!frmSiteInformation!SiteCommonName = NewData

does the trick of moving what the user typed into the field on the form
where they are creating the new record.

hope this helps any other newbie coders out there.

 

[DawnTreader]

Hello BruceM

thanks for the link, it might help with my new problem.

the frmSiteInformation is used 3 different ways. there is 2 different forms
where it is convienent to open the site form and allow the user to create a
new site. the 3rd way it is used it to show data related to information on
the main form.

"Are the forms open? If you are trying to send the user to another form, why
not simply open the other form?"

only one at a time. the code i posted was designed to close the
frmSiteInformation and then refresh the list available to the combo boxes on
one of 2 forms that the user might have come from, hence the case select code
based on the openargs of the frmSiteInformation.

the problem i was having was directly related to the commenting out the
saverecord line. if i didnt save the record then there was no way to refresh
the record set for the combo box to include the new record created on the
frmSiteInformation.

"If the other form is already open, why are you requerying it?"

the requery is of a subform on the apps main form that the
frmSiteInformation is related to. the one form where a user might create a
new site from "moves" the mainforms record from one site to another. hence
the need to requery that subform on the mainform otherwise it would keep
showing the site that the unit is no longer on.

"Why are you adding two values, one after the other, to the combo box
(Forms!frmVisualPartsLink!cboSite.Value = 1, then
Forms!frmVisualPartsLink!cboSite.Value = Me.SiteID)?"

that code was a bit legacy. i cleaned it up it now looks like the first case
but with a different form. this actually works as expected now. i think i
might have a new problem, but i have to do a little testing and see if i am
just seeing things. will post later.

just incase anyone is interested, here is my current code:

On Error GoTo Err_btnCloseSites_Click

Dim meopenArgs As String

If Me.cbServiceRepID = 1 Or IsNull(Me.cbServiceRepID) Then
MsgBox "Please choose a Service Representative, if in doubt choose
IMW Canada.", vbOKOnly, "Service Representative Needed"
GoTo Exit_btnCloseSites_Click
End If

If Me.Dirty = True Then
If IsNull(Me.cbCustomerListID) Then
Me.cbCustomerListID.Value = Me.CustomerListID
End If
DoCmd.RunCommand acCmdSaveRecord

'check to see if we are adding a site from a particular button
If Not IsNull(Me.OpenArgs) Then
meopenArgs = Me.Form.OpenArgs
Select Case meopenArgs
Case "ChangeSite"
Forms!pfrmChangeProductSite!cbChangeSiteList.Requery

Forms!zzMAINFORM.sfrmproductstositeinformation.Form.Requery
Forms!pfrmChangeProductSite!cbChangeSiteList.SetFocus
Case "VisualPartsLink"
Forms!frmVisualPartsLink!cboSite.Requery

Forms!zzMAINFORM.sfrmproductstositeinformation.Form.Requery
Forms!frmVisualPartsLink!cboSite.SetFocus
Case "ViewCustomerSiteDetails"
Forms!zzMAINFORM!sfrmproductstositeinformation.Requery
Forms!zzMAINFORM!SerialNumber.SetFocus
Case "ViewAllSites"
Forms!zzMAINFORM!sfrmproductstositeinformation.Requery
Forms!zzMAINFORM!SerialNumber.SetFocus
Case Else
Forms!zzMAINFORM!sfrmproductstositeinformation.Requery
Forms!zzMAINFORM!SerialNumber.SetFocus
End Select
End If
End If

DoCmd.Close acForm, "zySiteInformation"

Exit_btnCloseSites_Click:
Exit Sub

Err_btnCloseSites_Click:
MsgBox Err.Description
Resume Exit_btnCloseSites_Click
End Sub

 

[DawnTreader]

Hello again

i have finished fixing all the problems i was having regarding this form.

thanks for your input BruceM. i have managed to get it to reload the combo
boxes on the appropriate form, refresh the mainform, and keep the text from
the combo and load that in the new record and then after the user goes back
to the form they came from the combo box is on the appropriate new record.
pretty slick.

here is the code:

Private Sub btnCloseSite_Click()
On Error GoTo Err_btnCloseSites_Click

If Me.cbServiceRepID = 1 Or IsNull(Me.cbServiceRepID) Then
MsgBox "Please choose a Service Representative, if in doubt choose
IMW Canada.", vbOKOnly, "Service Representative Needed"
GoTo Exit_btnCloseSites_Click
End If

If Me.Dirty = True Then
If IsNull(Me.cbCustomerListID) Then
Me.cbCustomerListID.Value = Me.CustomerListID
End If
DoCmd.RunCommand acCmdSaveRecord

'check to see if we are adding a site from a particular button
If Not IsNull(Me.OpenArgs) Then
Select Case Me.OpenArgs
Case "ChangeSite"
Forms!pfrmChangeProductSite!cbChangeSiteList.Requery
Forms!pfrmChangeProductSite!cbChangeSiteList.Value =
Me.SiteID

Forms!zzMAINFORM.sfrmproductstositeinformation.Form.Requery
Forms!pfrmChangeProductSite.SetFocus
Forms!pfrmChangeProductSite!cmdChangeSite.SetFocus
Case "VisualPartsLink"
Forms!frmVisualPartsLink!cboSite.Requery
Forms!frmVisualPartsLink!cboSite.Value = Me.SiteID

Forms!zzMAINFORM.sfrmproductstositeinformation.Form.Requery
Forms!frmVisualPartsLink.SetFocus
Forms!frmVisualPartsLink!CustomerOrder.SetFocus
Case "ViewCustomerSiteDetails"
Forms!zzMAINFORM!sfrmproductstositeinformation.Requery
Forms!zzMAINFORM!SerialNumber.SetFocus
Case "ViewAllSites"
Forms!zzMAINFORM!sfrmproductstositeinformation.Requery
Forms!zzMAINFORM!SerialNumber.SetFocus
Case Else
Forms!zzMAINFORM!sfrmproductstositeinformation.Requery
Forms!zzMAINFORM!SerialNumber.SetFocus
End Select
End If
End If

DoCmd.Close acForm, "zySiteInformation"

Exit_btnCloseSites_Click:
Exit Sub

Err_btnCloseSites_Click:
MsgBox Err.Description
Resume Exit_btnCloseSites_Click
End Sub

 

[BruceM]

A few observations. This line of code:
Forms!zzMAINFORM.sfrmproductstositeinformation.Form.Requery
sometimes appears as:
Forms!zzMAINFORM.sfrmproductstositeinformation.Requery

In the first case you are requerying the form property of the subform
control; in the second you are requerying the subform control. It could be
that you are getting the desired result in both cases (I don't really know
what the difference would be in practical terms), but maybe you want that to
be the same code in all instances. If so, it is used for every Case in
Select Case, so you could just use that line of code once directly after:
If Not IsNull(Me.OpenArgs) Then ...

The Cases for "ViewCustomerSiteDetails", "ViewAllSites", and Else seem to be
the same, so they could all be included with a single Case Else.

If a control such as SerialNumber is also a field name, consider renaming
the control txtSerialNumber or some such. Access can become confused, and
when it does the error messages can be a bit mysterious.

Value is the default property. There is no harm to using it, but it is not
needed in most situations.

 

[Dirk Goldgar]

A few observations. This line of code:
Forms!zzMAINFORM.sfrmproductstositeinformation.Form.Requery
sometimes appears as:
Forms!zzMAINFORM.sfrmproductstositeinformation.Requery

In the first case you are requerying the form property of the subform
control; in the second you are requerying the subform control. It could
be that you are getting the desired result in both cases (I don't really
know what the difference would be in practical terms)

There should be no difference. Requerying a subform control is the same as
requerying its .Form object.

If a control such as SerialNumber is also a field name, consider renaming
the control txtSerialNumber or some such. Access can become confused, and
when it does the error messages can be a bit mysterious.

I believe this advice is incorrect. To the best of my knowledge -- and you
can look at Microsoft's sample databases and templates for evidence -- there
is no harm in naming a bound control the same as the field to which it's
bound. Where this may lead to confusion or issues is with calculated
controls and unbound controls -- they must never have the same name as any
field in the form's recordsource.

The most common source of errors with regard to this, in my experience, is
taking a control that was formerly bound directly to a field, and shares
that field's name, and changing it so that it is now bound to an
expression -- without remembering to change the name. Then all of a sudden
the control starts giving errors. So if you make a habit of always naming
your controls with a prefix of some sort, that can never happen. You can
certainly argue that that's a good development practice, but you can argue
against it, too. But the arguments are based on convenience,
error-avoidancce, and clarity, not the faulty premise that Access can't
handle a bound control with the same name as its controlsource.

 

[BruceM]

Thanks for the information about requerying the subform control. My point
to the OP was that the code performed the same requery in every case, so for
ease of coding it could be done once only.

Regarding control naming, it was probably with calculations that I ran into
difficulties with the control and the field sharing the same name. As I
recall there were some "Object required" errors that arose from fields and
text boxes having the same name, but I have been using control-naming
conventions for some while, so the details elude me. I guess it's muddying
the waters to make the suggestion unless problems occur.

 

[DawnTreader]

Hello Gentlemen

Thanks to you both. this is some great advise. i have managed to do what was
needed, and i will cut back on the redundant code. thanks bruce.

Thanks Guys!

 

[DawnTreader]

i have had and even better solution to this as of late:

Private Sub cboSite_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboSite_NotInList

If MsgBox("Add New Site?", vbYesNo, "Add New Site?") = vbYes Then
DoCmd.OpenForm "frmSiteInformation", acNormal, , , acFormAdd, ,
"VisualPartsLink"
Response = acDataErrContinue
Forms!frmSiteInformation!SiteCommonName = NewData
Me.cboSite.Value = ""
Else
MsgBox "The Site entered is not in the Aftermarket Network. Please
add it or choose from list.", vbOKOnly, "Not In List"
Response = acDataErrContinue
End If

Exit_cboSite_NotInList:
Exit Sub

Err_cboSite_NotInList:
MsgBox Err.Number & " " & Err.DESCRIPTION
Resume Exit_cboSite_NotInList

End Sub

---------------------------------------------
Private Sub btnCloseSite_Click()
On Error GoTo Err_btnCloseSites_Click

If Me.cbServiceRepID = 1 Or IsNull(Me.cbServiceRepID) Then
MsgBox "Please choose a Service Representative, if in doubt choose
IMW Canada.", vbOKOnly, "Service Representative Needed"
GoTo Exit_btnCloseSites_Click
End If

If Me.Dirty = True Then
If IsNull(Me.cbCustomerListID) Then
Me.cbCustomerListID.Value = Me.CustomerListID
End If
DoCmd.RunCommand acCmdSaveRecord

'check to see if we are adding a site from a particular button
If Not IsNull(Me.OpenArgs) Then
Select Case Me.OpenArgs
Case "ChangeSite"
Forms!pfrmChangeProductSite!cbChangeSiteList.Requery
Forms!pfrmChangeProductSite!cbChangeSiteList.Value =
Me.SiteID

Forms!frmManageAssets.sfrmproductstositeinformation.Form.Requery
Forms!pfrmChangeProductSite.SetFocus
Forms!pfrmChangeProductSite!cmdChangeSite.SetFocus
Case "VisualPartsLink"
Forms!frmVisualPartsLink!cboSite.Requery
Forms!frmVisualPartsLink!cboSite.Value = Me.SiteID

Forms!frmManageAssets.sfrmproductstositeinformation.Form.Requery
Forms!frmVisualPartsLink.SetFocus
Forms!frmVisualPartsLink!CustomerOrder.SetFocus
Case "ViewCustomerSiteDetails"

Forms!frmManageAssets!sfrmproductstositeinformation.Requery
Forms!frmManageAssets!SerialNumber.SetFocus
Case "ViewAllSites"

Forms!frmManageAssets!sfrmproductstositeinformation.Requery
Forms!frmManageAssets!SerialNumber.SetFocus
Case Else

Forms!frmManageAssets!sfrmproductstositeinformation.Requery
Forms!frmManageAssets!SerialNumber.SetFocus
End Select
End If
End If

DoCmd.Close acForm, "frmSiteInformation"

Exit_btnCloseSites_Click:
Exit Sub

Err_btnCloseSites_Click:
MsgBox Err.DESCRIPTION
Resume Exit_btnCloseSites_Click
End Sub

and it all works like a charm.

 

[Marshall Barton]

I can't even pretend to follow all that, but whatever it is,
it's a bit unusual. A standard NotInList that opens another
form to add an item to the combo box's list should open the
add form in dialog mode. This prevents users from going off
to other stuff and never entering the new item.

Doing that pauses the not in list code until the add form is
closed or made invisible, This means the only(?) other
thing you need in the NotInList procedure is to set Response
to acDataErrAdded to automatically requery the combo box.

The add form's BeforeUpdate event should be where all the
new record's validation would normally be done. Putting it
in a button's Click event allows for a bunch of ways to
bypass the validation code.