How to refresh data from listbox on form? 1

[TomYC]

On a form I have a list box, whose value is a row source from a query:
SELECT [Legend Query].Field13, Mid$([Field13],6,2) & "/" & Mid$([field13],9.2) & "/" & Mid$([field13],1,4) AS RightDate FROM [Legend Query];
Basically all this is doing is calling a single value from a table (whose value changes weekly), and formatting it to that it can function as a text string in a subsequent procedure that defines a path. That is, the value in this listbox (txtDate) contributes to defining where another command will look for and retrive a report file.
When I open the form, the list box displays correct, i.e a date string. But a command button whose event includes parsing this date string fails...UNLESS I click (manually change to focus to) on the listbox prior to clicking on the command button. I've tried Me.txtDate.SetFocus, and Me.txtDate.Requery, but nothing has worked so far. I'm only using a listbox because I couldn't get a textbox to display the value.
What's the answer?
T.Y.

 

[kjv1611]

Have you tried Me.ttDate.Refresh?

And why is your list box named txtDate? Are you refreshing the wrong control?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[TomYC]

Thank you--
Quick answers: the name txtDate is because originally, of course, I assumed it would be a textbox, but I haven't been able to get a textbox to work. As for me.txtDate.refresh, that isn't allowed. Under what circumstances can Me.xxx.Refresh be allowed?
T.Y.

 

[dhookom]

How about changing the control to a text box with a control source of:

=DLookup("Mid$([Field13],6,2) & '/' & Mid$([field13],9.2) & '/' & Mid$([field13],1,4)","[Legend Query]")

OR

=DLookup("[Field13]","[Legend Query]")

Duane
Hook'D on Access
MS Access MVP

 

[TomYC]

That did the trick, dhookom!
I'll try and figure out why it took a domain function, but now everything works without all that hoop-through jumping!
Tnx,
T.Y.

 

[kjv1611]

.Refresh not allowed? Maybe that's just on the form level, then. I didn't take the time to piddle directly in Access at the moment. I may revisit later to see, b/c you should be able to requery/refresh the data via your query, and queries for recordsets pretty much always perform faster than DLOOKUP. Of course, if your underlying table is small enough, then the difference is negligible.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[MazeWorX]

That's because you need to select an item from a list box, by design they contain multiple items as opposed to a text box. Re query/ Re Fresh isn't the issue here using the correct control as Duane suggested with a DLookup is

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>