Point a Listbox to a Table in VBA (1 Viewer)

sja13

Professional Idiot

Local time

Today, 16:15

Joined

May 3, 2017

Messages

63

• Jul 20, 2017

• #1

I have a simple Dialogue Form (“frmISs”) with a listbox (“lbxSelector”), and two command buttons, the usual “ cmdOK” and “cmdCancel”.

Using vba I want to “point” the listbox to a field in a Table.
I’ve tried all sorts, the latest being

Code:Copy to clipboard

  strTable = “tblItem”
  strField = “ItemName”
  With Form_frmISs
    .RecordSource = strTable
    .lbxSelector.RowSource = strField
  End With 
 DoCmd.OpenForm "frmIss", _
                 acNormal, _
                 , _
                 , _
                 , _
                 acDialog

All that happens is the Form opens with the RecordSource and RowSource it was created with.
I’ve also tried adding “.Requery” before the End With. No effect!

I assume that it is normal to modify the Form's Properties and Controls prior to actually issuing the OpenForm, as I can’t find the equivalent of “Form.Update” or “Form.Save”.

Can any kind soul point me in the right direction?
Thanks in advance….

 

jdraw

Super Moderator

Staff member

Local time

Today, 11:15

Joined

Jan 23, 2006

Messages

15,379

• Jul 20, 2017

• #2

How about telling us in plain English what you are trying to do --no jargon, no database, no listbox etc.

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 08:15

Joined

Aug 30, 2003

Messages

36,125

• Jul 20, 2017

• #3

The listbox would look more like:

.lbxSelector.RowSource = "SELECT " & strField & " FROM " & strTable

adding brackets if there could be spaces or symbols in either. You'd do it in the open or load event of the form, not before opening it.

 

sja13

Professional Idiot

Local time

Today, 16:15

Joined

May 3, 2017

Messages

63

• Jul 21, 2017

• #4

Thanks pbaldy - "Wino Moderator"? - Is Wino short for Windows, or do you have some sort of "refreshment"l related issues?

 

sja13

Professional Idiot

Local time

Today, 16:15

Joined

May 3, 2017

Messages

63

• Jul 21, 2017

• #5

jdraw

In essence, I want a pop-up form with a listbox containing a particular field taken from a specific table,, where the name of the field and the name of the are supplied by VBA..

 

Minty

AWF VIP

Local time

Today, 16:15

Joined

Jul 26, 2013

Messages

10,371

• Jul 21, 2017

• #6

You would do that on the pop up forms load event not before opening the form. You can't change the design of the form in code.

(Well you can but it's fraught with issues, and won't work in a runtime environment so just don't)

 

sja13

Professional Idiot

Local time

Today, 16:15

Joined

May 3, 2017

Messages

63

• Jul 21, 2017

• #7

Cheers Minty - I am now using the Load event with OpenArgs so I've access to the "Me." properties. Looking good so far!

 

pbaldy

Wino Moderator

Staff member

Local time

Today, 08:15

Joined

Aug 30, 2003

Messages

36,125

• Jul 21, 2017

• #8

sja13 said:

Thanks pbaldy - "Wino Moderator"? - Is Wino short for Windows, or do you have some sort of "refreshment"l related issues?

Click to expand...

Happy to help! I must admit to a fondness for the grape juice.