Goto specific Record in DAO RecordSet

[Phil1]

Hi all.



I have a ListBox which gets its data from a DAO.Recordset I created in VBA (after running a SQL query)
This DAO record set is not closed/cleared by using set DAOrecordset = nothing until the form closes - So, as I understand it, the recordset remains open and can be referenced right?

-------------------------------------

The SQL Code used is:

"SELECT TblPhoneCall.CallerName, TblPhoneCall.CompanyName, TblPhoneCall.DateTime, TblPhoneCall.Description, TblWelcomePacks.ID " & _
"FROM TblPhoneCall INNER JOIN TblWelcomePacks ON TblPhoneCall.ID = TblWelcomePacks.ID " & _
"WHERE TblPhoneCall.CallerName LIKE '" & EdCallerName.Value & "*' " & _
"ORDER BY TblPhoneCall.CallerName;"

------------------------------------
the List box is updated with the records found with the above SQL code.

So, onto my problem...

When I click on an item in the list box, I want the corresponding records' Description (as in bold in the SQL code) data to be displayed inside a TextBox located on the form.

I've experimented with the SEEK function however I kept getting errors.


Can anyone help?

[pbaldy]

Unless I'm misunderstanding, you don't need the recordset. This should work:

http://www.baldyweb.com/Autofill.htm

[Bob Fitz]

Just set the Textbox's Control Source property to:

= NameOfYourListBox.Column(3)

[Bob Fitz]

Sorry Paul. You worked it out while I was still thinking about it.

[pbaldy]

No problem Bob. Obviously great minds think alike.

[Bob Fitz]

No problem Bob. Obviously great minds think alike.

May be, but clearly some do it faster!

[Phil1]

Thanks guys for your input, unfortunately I believe I'm forced to have to use the recordset I've created in VBA as its a bit more complicated. I'll explain...
I tried originally using a subform to display the query results (which I think would link to what you're trying to do) but I wanted a more bespoke output (to only show the date a welcome pack was sent IF one was sent - see image).

To explain I've uploaded the image above of my form.
The form is solely for saving new records into a table as you expect.

My problem is:...

When the user enters a value like in the image ("John"), A SQL query runs and is attached to a DAO recordset (both created in VBA) which displays any records similar to the details entered in the form.
The query also pulls the description of the call that is similar to the one being entered (shown in the image as John Smith - his description is also stored in the DAO recordset.
What I want is, when the user clicks on John Smith, his call desciption is replaced in the text box above it (Description of Call).

don't worry about replacing the currently entered details with John Smiths, that's sorted by only displaying John Smith's description while the mouse is down, when the mouse button is released I'll have it replaced with what was there before. My problem is solely getting the list box to display John Smiths call description.

The OnMouseDown procedure for the List Box has the following: TxtDescription.Value = MatchingRecordsRS("Description")
MatchingRecordsRS is the name of the DAO.RecordSet
TxtDescription is the name of the 'Description of Call' Text Box

NOTE: the Dao.recordset does not get closed after it runs the SQL Query.

The error that is returned is: Run Time error 2113: The value you entered isn't valid for this field and highlights the above.

[pbaldy]

I must be having brain cramps again, because I still don't see the need for the recordset. If you're clicking on the listbox, then the value from it is available to be put in the textbox. In any case, the error seems to imply that the value from the recordset isn't compatible with the textbox. The textbox isn't bound to a numeric field or something is it? Have you set a breakpoint or used Debug.Print to check the value coming from the recordset? Can you post the db here?

[Bob Fitz]

I entirely agree with Paul.

Can you post the db in mdb format. I can't use A2007 or A2010 files.