display data on form

[shank]

I have a form with company name, address, etc. I'm trying to add a list box
that will show related AssignedCompanies based on a query (below). The list
box shows correctly when you open the form, but when you switch companies it
does not update to the new AssignedCompanies. How do I get the list box to
update when navigating through the different comapnies?

SELECT SalesCompany.ID, AssignedCompany.CompanyID, AssignedCompany.AcctName
FROM SalesCompany INNER JOIN AssignedCompany ON SalesCompany.ID =
AssignedCompany.CompanyID
WHERE (((SalesCompany.ID)=[Forms]![frm_SalesCompany]![ID]))
ORDER BY AssignedCompany.AcctName;

thanks

 

[Guest]

In the OnCurrent event of the form, put:
Me.List1.Requery

using the actual name of your listbox.

Barry

 

[shank]

Listboc name: AssignedCompanies
When I placed Me.AssignedCompanies.Requery
It was changed to: =[Me].[AssignedCompanies].[Requery]
And now I get the error:
The object doesn't contain the Automation object 'Me.'

What am I doing wrong?
thanks!

In the OnCurrent event of the form, put:
Me.List1.Requery

using the actual name of your listbox.

Barry

I have a form with company name, address, etc. I'm trying to add a list
box
that will show related AssignedCompanies based on a query (below). The
list
box shows correctly when you open the form, but when you switch companies
it
does not update to the new AssignedCompanies. How do I get the list box
to
update when navigating through the different comapnies?

SELECT SalesCompany.ID, AssignedCompany.CompanyID,
AssignedCompany.AcctName
FROM SalesCompany INNER JOIN AssignedCompany ON SalesCompany.ID =
AssignedCompany.CompanyID
WHERE (((SalesCompany.ID)=[Forms]![frm_SalesCompany]![ID]))
ORDER BY AssignedCompany.AcctName;

thanks

 

[Allen Browne]

Barry's suggestion was to set the form's On Current property to:
[Event Procedure]
Then click the Build button (...) beside the property.
Access opens the code window.
Between the "Private Sub Form_Current()" and "End Sub" lines, enter:
Me.AssignedCompanies.Requery

You will want to add the same line to the AfterUpdate event procedure of the
control where you choose the company as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Listboc name: AssignedCompanies
When I placed Me.AssignedCompanies.Requery
It was changed to: =[Me].[AssignedCompanies].[Requery]
And now I get the error:
The object doesn't contain the Automation object 'Me.'

What am I doing wrong?
thanks!

In the OnCurrent event of the form, put:
Me.List1.Requery

using the actual name of your listbox.

Barry

I have a form with company name, address, etc. I'm trying to add a list
box
that will show related AssignedCompanies based on a query (below). The
list
box shows correctly when you open the form, but when you switch
companies it
does not update to the new AssignedCompanies. How do I get the list box
to
update when navigating through the different comapnies?

SELECT SalesCompany.ID, AssignedCompany.CompanyID,
AssignedCompany.AcctName
FROM SalesCompany INNER JOIN AssignedCompany ON SalesCompany.ID =
AssignedCompany.CompanyID
WHERE (((SalesCompany.ID)=[Forms]![frm_SalesCompany]![ID]))
ORDER BY AssignedCompany.AcctName;

 

[shank]

Ok! This is great! Next step: I also have a subform: frm_SubAssignedCompany
on that form that will add or delete companies. When I use it,
AssignedCompanies will not update. I tried using
Me.AssignedCompanies.Requery in the OnCurrent and AfterUpdate properties of
that form. No joy! I have a list box (List16) on that form. I would expect
that when I enter a company and navigate to the next record,
AssignedCompanies would update. How do I get that done?
thanks!

Barry's suggestion was to set the form's On Current property to:
[Event Procedure]
Then click the Build button (...) beside the property.
Access opens the code window.
Between the "Private Sub Form_Current()" and "End Sub" lines, enter:
Me.AssignedCompanies.Requery

You will want to add the same line to the AfterUpdate event procedure of
the control where you choose the company as well.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Listboc name: AssignedCompanies
When I placed Me.AssignedCompanies.Requery
It was changed to: =[Me].[AssignedCompanies].[Requery]
And now I get the error:
The object doesn't contain the Automation object 'Me.'

What am I doing wrong?
thanks!

In the OnCurrent event of the form, put:
Me.List1.Requery

using the actual name of your listbox.

Barry

:

I have a form with company name, address, etc. I'm trying to add a list
box
that will show related AssignedCompanies based on a query (below). The
list
box shows correctly when you open the form, but when you switch
companies it
does not update to the new AssignedCompanies. How do I get the list box
to
update when navigating through the different comapnies?

SELECT SalesCompany.ID, AssignedCompany.CompanyID,
AssignedCompany.AcctName
FROM SalesCompany INNER JOIN AssignedCompany ON SalesCompany.ID =
AssignedCompany.CompanyID
WHERE (((SalesCompany.ID)=[Forms]![frm_SalesCompany]![ID]))
ORDER BY AssignedCompany.AcctName;

 

[Allen Browne]

You should be able to requery your combo in the AfterUpdate event of the
*form* (not control) where records are added to its RowSource table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok! This is great! Next step: I also have a subform:
frm_SubAssignedCompany on that form that will add or delete companies.
When I use it, AssignedCompanies will not update. I tried using
Me.AssignedCompanies.Requery in the OnCurrent and AfterUpdate properties
of that form. No joy! I have a list box (List16) on that form. I would
expect that when I enter a company and navigate to the next record,
AssignedCompanies would update. How do I get that done?
thanks!

Barry's suggestion was to set the form's On Current property to:
[Event Procedure]
Then click the Build button (...) beside the property.
Access opens the code window.
Between the "Private Sub Form_Current()" and "End Sub" lines, enter:
Me.AssignedCompanies.Requery

You will want to add the same line to the AfterUpdate event procedure of
the control where you choose the company as well.

Listboc name: AssignedCompanies
When I placed Me.AssignedCompanies.Requery
It was changed to: =[Me].[AssignedCompanies].[Requery]
And now I get the error:
The object doesn't contain the Automation object 'Me.'

What am I doing wrong?
thanks!

message In the OnCurrent event of the form, put:
Me.List1.Requery

using the actual name of your listbox.

Barry

:

I have a form with company name, address, etc. I'm trying to add a
list box
that will show related AssignedCompanies based on a query (below). The
list
box shows correctly when you open the form, but when you switch
companies it
does not update to the new AssignedCompanies. How do I get the list
box to
update when navigating through the different comapnies?

SELECT SalesCompany.ID, AssignedCompany.CompanyID,
AssignedCompany.AcctName
FROM SalesCompany INNER JOIN AssignedCompany ON SalesCompany.ID =
AssignedCompany.CompanyID
WHERE (((SalesCompany.ID)=[Forms]![frm_SalesCompany]![ID]))
ORDER BY AssignedCompany.AcctName;

 

[shank]

I have the following on the master form:

Private Sub Form_AfterUpdate()
Me.AssignedCompanies.Requery
End Sub

Private Sub Form_Current()
Me.AssignedCompanies.Requery
End Sub

Control AssignedCompanies updates as it should when I navigate using the
master form frm_SalesCompany.

On the master form, I have a subform frm_SubAssignedCompany. That subform
allows me to edit the underlying table AssignedCompanies and the control
AssignedCompanies displays that data. I want the control AssignedCompanies
to requery with any changes made by frm_SubAssignedCompany. And that's not
happening with the above edits. Then I tried making the above edits on
frm_SubAssignedCompany itself and it still didn't work. What am I misisng?
thanks!
- - - - - - - - - - - - - - - - - - - - - - - - - - -

You should be able to requery your combo in the AfterUpdate event of the
*form* (not control) where records are added to its RowSource table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok! This is great! Next step: I also have a subform:
frm_SubAssignedCompany on that form that will add or delete companies.
When I use it, AssignedCompanies will not update. I tried using
Me.AssignedCompanies.Requery in the OnCurrent and AfterUpdate properties
of that form. No joy! I have a list box (List16) on that form. I would
expect that when I enter a company and navigate to the next record,
AssignedCompanies would update. How do I get that done?
thanks!

Barry's suggestion was to set the form's On Current property to:
[Event Procedure]
Then click the Build button (...) beside the property.
Access opens the code window.
Between the "Private Sub Form_Current()" and "End Sub" lines, enter:
Me.AssignedCompanies.Requery

You will want to add the same line to the AfterUpdate event procedure of
the control where you choose the company as well.

Listboc name: AssignedCompanies
When I placed Me.AssignedCompanies.Requery
It was changed to: =[Me].[AssignedCompanies].[Requery]
And now I get the error:
The object doesn't contain the Automation object 'Me.'

What am I doing wrong?
thanks!

message In the OnCurrent event of the form, put:
Me.List1.Requery

using the actual name of your listbox.

Barry

:

I have a form with company name, address, etc. I'm trying to add a
list box
that will show related AssignedCompanies based on a query (below).
The list
box shows correctly when you open the form, but when you switch
companies it
does not update to the new AssignedCompanies. How do I get the list
box to
update when navigating through the different comapnies?

SELECT SalesCompany.ID, AssignedCompany.CompanyID,
AssignedCompany.AcctName
FROM SalesCompany INNER JOIN AssignedCompany ON SalesCompany.ID =
AssignedCompany.CompanyID
WHERE (((SalesCompany.ID)=[Forms]![frm_SalesCompany]![ID]))
ORDER BY AssignedCompany.AcctName;

 

[Allen Browne]

If the combo is on the parent form, you will need this in the subform's
Form_AfterUpdate:

Private Sub Form_AfterUpdate()
Me.Parent!AssignedCompanies.Requery
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have the following on the master form:

Private Sub Form_AfterUpdate()
Me.AssignedCompanies.Requery
End Sub

Private Sub Form_Current()
Me.AssignedCompanies.Requery
End Sub

Control AssignedCompanies updates as it should when I navigate using the
master form frm_SalesCompany.

On the master form, I have a subform frm_SubAssignedCompany. That subform
allows me to edit the underlying table AssignedCompanies and the control
AssignedCompanies displays that data. I want the control AssignedCompanies
to requery with any changes made by frm_SubAssignedCompany. And that's not
happening with the above edits. Then I tried making the above edits on
frm_SubAssignedCompany itself and it still didn't work. What am I misisng?
thanks!
- - - - - - - - - - - - - - - - - - - - - - - - - - -

You should be able to requery your combo in the AfterUpdate event of the
*form* (not control) where records are added to its RowSource table.

--

Ok! This is great! Next step: I also have a subform:
frm_SubAssignedCompany on that form that will add or delete companies.
When I use it, AssignedCompanies will not update. I tried using
Me.AssignedCompanies.Requery in the OnCurrent and AfterUpdate properties
of that form. No joy! I have a list box (List16) on that form. I would
expect that when I enter a company and navigate to the next record,
AssignedCompanies would update. How do I get that done?
thanks!

Barry's suggestion was to set the form's On Current property to:
[Event Procedure]
Then click the Build button (...) beside the property.
Access opens the code window.
Between the "Private Sub Form_Current()" and "End Sub" lines, enter:
Me.AssignedCompanies.Requery

You will want to add the same line to the AfterUpdate event procedure
of the control where you choose the company as well.

Listboc name: AssignedCompanies
When I placed Me.AssignedCompanies.Requery
It was changed to: =[Me].[AssignedCompanies].[Requery]
And now I get the error:
The object doesn't contain the Automation object 'Me.'

What am I doing wrong?
thanks!

message In the OnCurrent event of the form, put:
Me.List1.Requery

using the actual name of your listbox.

Barry

:

I have a form with company name, address, etc. I'm trying to add a
list box
that will show related AssignedCompanies based on a query (below).
The list
box shows correctly when you open the form, but when you switch
companies it
does not update to the new AssignedCompanies. How do I get the list
box to
update when navigating through the different comapnies?

SELECT SalesCompany.ID, AssignedCompany.CompanyID,
AssignedCompany.AcctName
FROM SalesCompany INNER JOIN AssignedCompany ON SalesCompany.ID =
AssignedCompany.CompanyID
WHERE (((SalesCompany.ID)=[Forms]![frm_SalesCompany]![ID]))
ORDER BY AssignedCompany.AcctName;

 

[shank]

Getting real close... Me.Parent!AssignedCompanies.Requery worked great for
adding records, but not deleting them. When I delete a record, it does not
update. So I added that same code to the OnDelete and no joy. What do I need
to add so it will update when a delete a record?
thanks again!!!

If the combo is on the parent form, you will need this in the subform's
Form_AfterUpdate:

Private Sub Form_AfterUpdate()
Me.Parent!AssignedCompanies.Requery
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have the following on the master form:

Private Sub Form_AfterUpdate()
Me.AssignedCompanies.Requery
End Sub

Private Sub Form_Current()
Me.AssignedCompanies.Requery
End Sub

Control AssignedCompanies updates as it should when I navigate using the
master form frm_SalesCompany.

On the master form, I have a subform frm_SubAssignedCompany. That subform
allows me to edit the underlying table AssignedCompanies and the control
AssignedCompanies displays that data. I want the control
AssignedCompanies to requery with any changes made by
frm_SubAssignedCompany. And that's not happening with the above edits.
Then I tried making the above edits on frm_SubAssignedCompany itself and
it still didn't work. What am I misisng?
thanks!
- - - - - - - - - - - - - - - - - - - - - - - - - - -

You should be able to requery your combo in the AfterUpdate event of the
*form* (not control) where records are added to its RowSource table.

--
Ok! This is great! Next step: I also have a subform:
frm_SubAssignedCompany on that form that will add or delete companies.
When I use it, AssignedCompanies will not update. I tried using
Me.AssignedCompanies.Requery in the OnCurrent and AfterUpdate
properties of that form. No joy! I have a list box (List16) on that
form. I would expect that when I enter a company and navigate to the
next record, AssignedCompanies would update. How do I get that done?
thanks!

Barry's suggestion was to set the form's On Current property to:
[Event Procedure]
Then click the Build button (...) beside the property.
Access opens the code window.
Between the "Private Sub Form_Current()" and "End Sub" lines, enter:
Me.AssignedCompanies.Requery

You will want to add the same line to the AfterUpdate event procedure
of the control where you choose the company as well.

Listboc name: AssignedCompanies
When I placed Me.AssignedCompanies.Requery
It was changed to: =[Me].[AssignedCompanies].[Requery]
And now I get the error:
The object doesn't contain the Automation object 'Me.'

What am I doing wrong?
thanks!

message In the OnCurrent event of the form, put:
Me.List1.Requery

using the actual name of your listbox.

Barry

:

I have a form with company name, address, etc. I'm trying to add a
list box
that will show related AssignedCompanies based on a query (below).
The list
box shows correctly when you open the form, but when you switch
companies it
does not update to the new AssignedCompanies. How do I get the list
box to
update when navigating through the different comapnies?

SELECT SalesCompany.ID, AssignedCompany.CompanyID,
AssignedCompany.AcctName
FROM SalesCompany INNER JOIN AssignedCompany ON SalesCompany.ID =
AssignedCompany.CompanyID
WHERE (((SalesCompany.ID)=[Forms]![frm_SalesCompany]![ID]))
ORDER BY AssignedCompany.AcctName;

 

[Allen Browne]

Use the AfterDelConfirm event of the subform.

 

[shank]

Perfect!
Thanks!

Use the AfterDelConfirm event of the subform.