TR Forums

Hope somebody who knows Access and VB can help me:

I've been doing this for hours and can't figure out what is going on.

I'm trying to do a simple record lookup with a combo-box. The combo-box selects the "Plant_ID" to lookup. I created a form (with textboxes displaying information off of a record with various information fields (Plant_ID/plant name/length/type/etc.) of that specific Plant record). This should happen when I select the plant from the drop-down box (cboPlant_ID_lookup).

Plant_List is my master table where all my data is stored in.

Here's my code for the combo-box (which should be all that's needed):



All the other textboxes in the form have their Control Source property to each corresponding Table field (length/type/etc.)

The wierd thing is - it correctly lists all my Plant_IDs in the combo-box drop-down list, but when I select that specific Plant_ID, all my other textboxes that suppose to display that specific plant information doesn't show anything...

This is Access 2003 btw, and I'm pulling my hairs on this...

Why are you changing the entire underlying recordset (by assigning to the form's recordsource property) when all you appear to be wanting to do (based on your query) is change the sort order? And are you really listing the field names in your combobox? Because ORDER BY expects a field name, not a value.

I don't think I understand what you're attempting to accomplish, so I'm not sure what to tell you.

Ok, I modified the code a bit, but it's still not working - hopefully this is a bit better:



A side note: Does Access really care if I use single-quotation vs double-quotation when I try to state a specific criteria (in this case "cboPlant_ID_lookup" or 'cboPlant_ID_lookup')?

NeRve wrote:

A side note: Does Access really care if I use single-quotation vs double-quotation when I try to state a specific criteria (in this case "cboPlant_ID_lookup" or 'cboPlant_ID_lookup')?

If I'm not mistaken, the single quotation make in VB signifies a comment within the code, so any part after it would be ignored.

Access may behave differently, but that's how Visual Basic itself behaves.

Your first query looked definitely wrong because you didn't have a where clause.

Don't know why your 2nd query did not work but the comment thing may be an issue. Not sure about the data binding stuff in Access so I can't say if the ControlSource thing is correct or not.

I would suggest looking into parameterized query though, to completely work around the comment issue and get yourself prepared to avoid future SQL injection attacks with your other queries. Your query would look like:And then you specify "parameter #1" with your ID.

But all together, the coding looks perfect to me and can't figure out why Access won't show me the text fields...

Flying Fox wrote:

Your first query looked definitely wrong because you didn't have a where clause.

Don't know why your 2nd query did not work but the comment thing may be an issue. Not sure about the data binding stuff in Access so I can't say if the ControlSource thing is correct or not.

I would suggest looking into parameterized query though, to completely work around the comment issue and get yourself prepared to avoid future SQL injection attacks with your other queries. Your query would look like:

Code: Select all

   
select * from Plant_List where Plant_ID = ?

And then you specify "parameter #1" with your ID.

I did another quick experiment:

Throwing away the combo-box entirely, I just manually changed the RecordSource of the Form property itself to



This SHOULD display information about Plant_ID "2" for all the textbox fields with each correlating ControlSource on the Form right ?

Well nothing shows at all!

I FIXED IT!

It was the stupid quotation marks! Had to look closer, but there is a difference between

And if you do use parameterized query, the quote thing is irrelevant as well.

Now this is wierd, I am able to successfully pull up these records in one form (fields automatically appearing on all my textboxes).

However, if I create another form from scratch with the same exact settings, the textboxes don't show anything when I pull up a record. I know it pulled it up a record because when I right click on any textbox I get a "Ascending Order"/"Descending Order" option and if I click that, the textbox populates with the record. I thought Access was suppose to populate the textboxes with whatever record I'm on automatically?

Did you actually base the form on a query? If you're manually setting the record source you need to force it to refresh IIRC.

UberGerbil wrote:

Did you actually base the form on a query? If you're manually setting the record source you need to force it to refresh IIRC.

There is not separate SQL query in my database. All the query is done right in the RecordSource property. How do you a "force refresh?" And why does it work on my first form, but not my literally cloned-from-the-first forms? I hope this isn't a dumb Access bug...



My cboPlant_ID_lookup has a RowSource as follows (and this has been listing my Plant_IDs flawlessly):

Access is complicated enough that it's more likely something somewhere isn't identical between the two, rather than being a bug. Are you sure the properties on the controls are set so that they bind to the correct fields? When you've got the form up and it doesn't seem to be displaying anything, have a look at it in the debug window. Is the form's underlying RecordSet populated (RowCount >0)? Are you actually on a record (EOF false and BOF false?)