Multi-Use Drop Down Box

[wreded]

i have a form with two separate drop down boxes i use to find items in a table. One drop down finds based on an arbitrarily assigned number the other finds by item serial number. i got to thinking that i could use one drop box to search either field, in sequence (i.e.: arbitrary number, then serial number). Programmatically this should not pose a real problem. i've tried it and have run into my first issue: Setting the drop down box's RowSource.
i've tried dimming a variable as "mRow as RowSource" then assigning "mRow = " as the RowSource from the form. It's an SQL string and i can't make it work like i expect.

Any ideas?

Thanks,
Dave

 

[MajP]

the rowsource is a property of the control it takes a string values

dim mRowSource as string
mRowSoure = "some string"
me.someComboBox.rowsource = mRowSource

 

[wreded]

Ok, makes sense now.

When i set the "me.someComboBox.rowsource = mRowSource" i get a compile error, "Method or data member not found."

 

[MajP]

need to provide you actual code. That was an example and not to be taken literally as is.

 

[wreded]

i took it as an example and modified it to my needs.

Private Sub txtFindBarCode_AfterUpdate()
    Dim rs as Object
    Dim BC_Length as Integer
    Dim btnResult as Integer
    Dim mRow as string

    set Rs = me.Recordset.Clone
    BC_Length = Len(txtFindBarCode)
    If Mid(Me.txtFindBarcode, 1, 2) = "1P" then
        Me.txtFindBarCode = Mid(Me.txtFindBarCode, 3, BC_Length)
    End If

    mRow = "SELECT qryMain.BarCode FROM qryMain;"

    '(This is where the error occurs)
    Me.txtBarCode.RowSource = mRow

After this line i do the error trapping for "Not Found," and displaying the record found, etc.

i try to name all my fields, labels, etc. based on what they are and what they do. An input field is generally prefixed by "txt," a label by "lbl" and then a descriptor or field name. In this case my "find" field is named "txtFindBarCode" and i have to strip a couple of characters the scanner grabs that are not in the table.

 

[MajP]

A text box does not have a row source, only listboxes, combos and forms. What are you desiring to happen? Are you looking for a single value to return? Maybe a dlookup will meet your needs here.

 

[wreded]

It is a combo box, i just tend to call anything that takes text input "txt" then whatever. i want it to act like a combo box and take the input i give it; if it doesn't find the input i want to change the data source from "BarCode" to "SerialNumber" and try to find the item again.
In short i want to use one combo box to do the job of the two i currently have.

 

[MajP]

Okay. So is it working now, or is there still a question? I would use a more traditional naming convention, you will confuse a lot of people

tblName table
frmName form
qryName query
rptName report
basName module
cboName combo
txtBxName textbox
lstName listbox
optName option Group
lblName label

your fields should not have the same name as your controls.

 

[wreded]

Nope, still not working. A little questioning on my part turned up the fact that the "RowSource" property is apparently read-only unless one uses a Property Let or Property Get procedure.

To be honest with You i've never run across another MSAccess developer or coder outside of boards like this one. i've come across several of the "drag 'n' drop" type of people who never change the names of anything and leave them at "Field126" and such, but no one who takes it even half-way seriously.

Thanks for the input on the naming conventions, i'll start using them.

Dave

 

[MajP]

A little questioning on my part turned up the fact that the "RowSource" property is apparently read-only unless one uses a Property Let or Property Get procedure

Who fed you that line? No there is absolutely no reason why you can not change the rowsource property of a listbox or combobox. It is a very commonly used technique.

If you have an error please describe carefully. There are different types of errors from do nothing to crashing the system to incorrect values. What is the error message? What line stops? What is expected behavior? Etc.
if
Me.txtBarCode.RowSource
gives you a method or datamember not found likely cause is
you do not have a combo box named txtBarCode and thus "data member not found"
or txtBarCode is not a combo box and therefore .rowsource is not one of its properties and thus "method not found"
You should use intellisense when you write code.
Type me.t and then all the properties starting with T will show. Then after txtBarCode type .R and then all the properties for txtBarCode that start with R will show. This way you avoid mispellings and improper names/properties.

As far as naming conventions there are many recommended ones, just google "access naming conventions" and/or "database naming conventions".

 

[tnguyen315]

I have a sample access database similar to the search form you are looking for. Email me, i'll send you the copy

 

[tnguyen315]

I have a sample access database similar to the search form you are looking for. Email me, i'll send you the copy