Requery a list box

[echorley]

I use access to gather statistics for football games.

Let's suppose the home team scores a touchdown. The form that was used to gather the play by play information (frmOffense) then opens another form (frmScoring) to store what type of score, how long, the time, who, etc.

frmScoring also has a series of list boxes that show the score for each team by quarter and the total score (a box score).

Each list box has a query as its rowsource, that adds the scores for each team for that quarter.

The problem I have is that when frmScoring is opened, the new score is not updated in the listbox. If I close the form and re-open it the score appears (I assume the listbox runs the query again).

I added a Refresh command button that does the trick, but I would like the list boxes to "requery" when I open the form.

I have tried alot of different ways to get this to work without the Refresh command button, most of them in the OnOpen part of the frmScoring (using VB and the requery command). Any ideas?

Thanks.

 

[pono1]

e,

Forms!FormName!listboxName.Requery

or

me.listboxName.Requery

Regards,
Tim

 

[echorley]

Hi Tim,
Thanks for the reply. Where do I put these bits of code? In the OnOpen part of the form? I have tried them in the OnOpen and still no requery when the form opens ....

 

[dan-cat]

There should be a 'on enter' event for the list-box itself.
Put the code:

Me!ListBoxName.Requery

in there

 

[pono1]

Assuming...

As a supplement to Dan-Cat's response, can I call an audible?

There must be a line of code in frmOffense that opens frmScoring. Immediately before that line put this line:

	Docmd.RunCommand acCmdSaveRecord

This will immediately send the contents of frmOffense to its bound table. Assuming this bound table is also used in the queries that feed your listboxes, the contents of the listboxes should be on side, sort of speak.

Regards,
Tim

 

[echorley]

Hi Tim,
The defense had you scouted, heard your audible and adjusted to stop you!

The form frmOffense sends the information to frmScoring. frmScoring is bound to the table tblScoring and that is where the queries are calculated.

Can this code be adjusted either in frmOffense or frmScoring?
code:--------------------------------------------------------------------------------
Docmd.RunCommand acCmdSaveRecord
--------------------------------------------------------------------------------

Thanks.

 

[pono1]

Story of my life. Sacked again.

The listboxes don't display the score correctly when frmScoring is first opened because the data in tblScoring (from which the listboxes get their data) is not up to date. That's the premise, right?

Also...

FrmScoring is bound to tlbscoring.

The listboxes are on FrmScoring.

When frmScoring opens (or is made visible?) an entry from a textbox on frmOffense is copied to a textbox on FrmScoring.

How is this data transferred between forms? Where is the code located that does this? In frmOffense's code module? And does it go something like this?

	DoCmd.OpenForm "FrmScoring"...
	Forms!FrmScoring!TextBoxScore = Me.FrmOffense.TextBoxScore

If so, you may be able to push forward, saving the scoring data to tblScoring and, if necessary, requerying the listboxes.

Add two more lines of code to do this.

	DoCmd.OpenForm "FrmScoring"...
	Forms!FrmScoring!TextBoxScore = Me.FrmOffense.TextBoxScore
'pushing on
	Forms!FrmScoring.Recalc 'save rec to table
	Forms!FrmScoring.ListBox1.Requery

Or you can take care of business before opening FrmScoring. That is, use an update query to put the new scoring info into the relevant record in TblScoring. To implement this, create an update query using the query grid and call it from code...

	Docmd.openQuery "MyUpdateQuery"

Or create your own SQL string -- very roughly, it would look something like this:

Dim strSQL as string

strSQL = "UPDATE TblScoring " & _
	 "SET TblScoring.Score = Whatever " & _
	 "WHERE TblScoring.FieldID= & " [Forms]![FrmOffense]![FieldID]

CurrentDB.Execute strSQL

A couple of options. Try them out and hopefully you'll soon be in the end zone. (That will be my last sports metaphor for the foreseeable future.)

Regards,
Tim

 

[echorley]

Tim,
You have game planned wonderfully.

Your assumptions about how I have bound the tables to the forms is correct. I will try all of your suggestions tonight and replay later.

I as well will refrain from any more football metaphors!

Thanks a bunch.