updating list boxes

  • Thread starter Thread starter Dave
  • Start date Start date
D

Dave

On a form i have an unbound list box

I have a sub that i use to refresh the data in the list box if a record is
changed or an item is deleted

Private Sub s_RefreshList()

varstr = "SELECT tlkp_JobDetails.JobDetailKeyID,
tlkp_JobDetails.Description, Sum([Price]*[Quantity]) AS [Parts Cost] "
varstr = varstr & "FROM (tlkp_JobParts INNER JOIN tlkp_Parts ON
tlkp_JobParts.PartKeyID = tlkp_Parts.PartKeyID) "
varstr = varstr & "INNER JOIN tlkp_JobDetails ON
tlkp_JobParts.JobDetailsKeyID = tlkp_JobDetails.JobDetailKeyID "
varstr = varstr & "WHERE tlkp_JobDetails.JobKeyID= " & Me.txtJobKeyID & " "
varstr = varstr & "GROUP BY tlkp_JobDetails.JobDetailKeyID,
tlkp_JobDetails.Description;"
Me.lstWorkDone.RowSource = varstr

End Sub

My question is
How do i run this sub from another form
The reason why is the original form open up another form, this forms adds a
record to the tlkp_JobDetails Table and i want the new record to be in the
list box when i close the second form down

if i use
Forms!frm_Jobs!s_RefreshList
on the close event i get an error saying
Expected: =

I have also tried using Call and that error is
Expected: (

any help or another way to do this would be much appreciated
 
In current guise you will not be able to call this sub from another form as
it is a private sub.

You either need to create a public sub in your form, which in turn calls
your refresh sub, or make your refresh sub public.

It is then a matter of calling it:

Call Form_frmName.SubName



--
Cheers,


James Goodman MCSE, MCDBA
http://www.angelfire.com/sports/f1pictures
 
Hi Dave,

First you need to make the sub public - then you excute it as a method of
the form so instead of a bang (!) as a separator you must use a dot(.).

Forms!frm_Jobs.s_RefreshList
 
Of course thank you
the only problem i might have now is that i have a
s_RefreshList sub on most of my forms
all of them are private, appart from the one ive just changed (thanks to
your suggestion)
i presume access will run the local version (the one in the Mod that is
running) of the sub by default rather then the public one but
will this have any adverse effects?

obviously i can just change the name of this sub, i am just curious


Sandra Daigle said:
Hi Dave,

First you need to make the sub public - then you excute it as a method of
the form so instead of a bang (!) as a separator you must use a dot(.).

Forms!frm_Jobs.s_RefreshList

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

On a form i have an unbound list box

I have a sub that i use to refresh the data in the list box if a
record is changed or an item is deleted

Private Sub s_RefreshList()

varstr = "SELECT tlkp_JobDetails.JobDetailKeyID,
tlkp_JobDetails.Description, Sum([Price]*[Quantity]) AS [Parts Cost] "
varstr = varstr & "FROM (tlkp_JobParts INNER JOIN tlkp_Parts ON
tlkp_JobParts.PartKeyID = tlkp_Parts.PartKeyID) "
varstr = varstr & "INNER JOIN tlkp_JobDetails ON
tlkp_JobParts.JobDetailsKeyID = tlkp_JobDetails.JobDetailKeyID "
varstr = varstr & "WHERE tlkp_JobDetails.JobKeyID= " & Me.txtJobKeyID
& " " varstr = varstr & "GROUP BY tlkp_JobDetails.JobDetailKeyID,
tlkp_JobDetails.Description;"
Me.lstWorkDone.RowSource = varstr

End Sub

My question is
How do i run this sub from another form
The reason why is the original form open up another form, this forms
adds a record to the tlkp_JobDetails Table and i want the new record
to be in the list box when i close the second form down

if i use
Forms!frm_Jobs!s_RefreshList
on the close event i get an error saying
Expected: =

I have also tried using Call and that error is
Expected: (

any help or another way to do this would be much appreciated
Click to expand...
Click to expand...
 
Since the others are private it definately won't matter. Even if the others
were public there should not be any conflict since you have to reference the
method (your procedure) through the form. As long as you have a valid
reference to the form you should be ok.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Of course thank you
the only problem i might have now is that i have a
s_RefreshList sub on most of my forms
all of them are private, appart from the one ive just changed (thanks
to your suggestion)
i presume access will run the local version (the one in the Mod that
is running) of the sub by default rather then the public one but
will this have any adverse effects?

obviously i can just change the name of this sub, i am just curious


Sandra Daigle said:
Hi Dave,

First you need to make the sub public - then you excute it as a
method of the form so instead of a bang (!) as a separator you must
use a dot(.).

Forms!frm_Jobs.s_RefreshList

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

On a form i have an unbound list box

I have a sub that i use to refresh the data in the list box if a
record is changed or an item is deleted

Private Sub s_RefreshList()

varstr = "SELECT tlkp_JobDetails.JobDetailKeyID,
tlkp_JobDetails.Description, Sum([Price]*[Quantity]) AS [Parts
Cost] " varstr = varstr & "FROM (tlkp_JobParts INNER JOIN
tlkp_Parts ON tlkp_JobParts.PartKeyID = tlkp_Parts.PartKeyID) "
varstr = varstr & "INNER JOIN tlkp_JobDetails ON
tlkp_JobParts.JobDetailsKeyID = tlkp_JobDetails.JobDetailKeyID "
varstr = varstr & "WHERE tlkp_JobDetails.JobKeyID= " &
Me.txtJobKeyID & " " varstr = varstr & "GROUP BY
tlkp_JobDetails.JobDetailKeyID, tlkp_JobDetails.Description;"
Me.lstWorkDone.RowSource = varstr

End Sub

My question is
How do i run this sub from another form
The reason why is the original form open up another form, this forms
adds a record to the tlkp_JobDetails Table and i want the new record
to be in the list box when i close the second form down

if i use
Forms!frm_Jobs!s_RefreshList
on the close event i get an error saying
Expected: =

I have also tried using Call and that error is
Expected: (

any help or another way to do this would be much appreciated
Click to expand...
Click to expand...
Click to expand...
 
thanks again

Sandra Daigle said:
Since the others are private it definately won't matter. Even if the others
were public there should not be any conflict since you have to reference the
method (your procedure) through the form. As long as you have a valid
reference to the form you should be ok.

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.

Of course thank you
the only problem i might have now is that i have a
s_RefreshList sub on most of my forms
all of them are private, appart from the one ive just changed (thanks
to your suggestion)
i presume access will run the local version (the one in the Mod that
is running) of the sub by default rather then the public one but
will this have any adverse effects?

obviously i can just change the name of this sub, i am just curious


Sandra Daigle said:
Hi Dave,

First you need to make the sub public - then you excute it as a
method of the form so instead of a bang (!) as a separator you must
use a dot(.).

Forms!frm_Jobs.s_RefreshList

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Dave wrote:
On a form i have an unbound list box

I have a sub that i use to refresh the data in the list box if a
record is changed or an item is deleted

Private Sub s_RefreshList()

varstr = "SELECT tlkp_JobDetails.JobDetailKeyID,
tlkp_JobDetails.Description, Sum([Price]*[Quantity]) AS [Parts
Cost] " varstr = varstr & "FROM (tlkp_JobParts INNER JOIN
tlkp_Parts ON tlkp_JobParts.PartKeyID = tlkp_Parts.PartKeyID) "
varstr = varstr & "INNER JOIN tlkp_JobDetails ON
tlkp_JobParts.JobDetailsKeyID = tlkp_JobDetails.JobDetailKeyID "
varstr = varstr & "WHERE tlkp_JobDetails.JobKeyID= " &
Me.txtJobKeyID & " " varstr = varstr & "GROUP BY
tlkp_JobDetails.JobDetailKeyID, tlkp_JobDetails.Description;"
Me.lstWorkDone.RowSource = varstr

End Sub

My question is
How do i run this sub from another form
The reason why is the original form open up another form, this forms
adds a record to the tlkp_JobDetails Table and i want the new record
to be in the list box when i close the second form down

if i use
Forms!frm_Jobs!s_RefreshList
on the close event i get an error saying
Expected: =

I have also tried using Call and that error is
Expected: (

any help or another way to do this would be much appreciated
Click to expand...
Click to expand...
Click to expand...
 
Back
Top