Code this!!

[Angello Pimental]

Using code, how do I get access to look up a value in a table based on what a user has selected in a combobox?

Before you answer, let me explain a bit further, as this problem is not as easy as it sounds.

I have a form which is used to add new records to a database. Whatever information a user enters onto the form is saved as a new record on a table.

In order to make entering information easier for the user, I want the user to be able to select a value from a combobox, which will then fill in a few other values based on what the user has selected.

These values are "Standard Information" that comes from a table(location). This information is not necessarily the correct information for the new record. So the user can then edit this "Standard Information" to what he/she needs before saving it to the table(records).

Basically, I need code that queries from Table1, then after information is completed, saves to Table2

Any help would be greatly appreciated.

Thanks,
Angelo

 

[AlanS]

Use DAO to open a recordset based on the second table, find a particular record based on the entry the user has made (or the item the user has selected) on the form, and assign selected values from the found record to appropriate fields on the form.

 

[kgcrowther]

I'm facing a similar problem as Angelo, but I'm a little confused on the vocabulary used. Could you please explain what you mean by "use DAO to open a recordset based on the second table". I think this is a solution to my problem as well.

I figure what you mean is something like:

Dim rs As DAO.recordset

Set rs = dbs.OpenRecordset("SELECT AllStuffNeeded" & "WHERE importantField = Forms![ThisForm]!importantField")

Anyway, I'm still new at this and was wondering if you could add just a little more detail.

Thanks,
Kenneth

 

[charityg]

Okay...here goes the long answer

CboName_AfterUpdate
dim db as database
dim rst as recordset
set db=currentdb
set rst=db.openrecordset("table1")
with rst
.findfirst "[tblFieldName]='" & cboName & "'"
'if cbo returns numberic value use
'.findfirst "[tblFieldName]=" & cboName

me!Field1=!Standardfield1
me!Field2=!Standardfield2
and so on

end with

Keep your recordsource for the form as table two. What will happen after the combo updates is the code will open a recordset based on table1 and find the record corresponding to the combo's value. Then it will assign the values of that record's fields to the textboxes on your form (which may be changed)

Let me know if you have any problems!


[This message has been edited by charityg (edited 06-20-2001).]

 

[Angello Pimental]

Charity,
Thank you for your help once again.... You are definitely my Access Angel

I have one slight problem with the code; I get the error message Run-time Error 3251 : "Operation is not supported for this type of object"

Here is my code:

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("Standardlocationtable")
With rst
**.FindFirst "[StandardLocation]='" & Location & "'"


Me!AdminContact = !StandardAdminContact
Me!BillingContact = !StandardBillingContact
'and so on

End With

**This is where the code stops and I get the error message

Thanks again for all your assistance,

Angelo


[This message has been edited by Angello Pimental (edited 06-20-2001).]

 

[D-Fresh]

Is "Location" the name of your combobox??? if so, then you have to use the syntax "me!Location" to refer to it... Hope that helps..

Doug

 

[Pat Hartman]

Charity's method will work perfectly well but I prefer to include the extra columns in the combobox query instead. Doing this saves a database access which isn't much if you're using something other than Access for your tables. But, Access is going to ship the entire table to the local PC to populate the combo, why make it do that again.

If you don't want to see the extra fields when you open the combo, include them in the column count but use 0 widths for them in the column width. Then to get the data into your bound textboxes,
Me.txtFld1 = Me.YourCombo.Column(2)
Me.txtFld2 = Me.YourCombo.Column(3)

I'm just guessing at the columns the fields will be in. The columns of the combo are a zero based array so the first column is referenced as (0), the second as (1), etc. So figure out what column numbers you need to use to pick out the correct data.

 

[Angello Pimental]

Thank you for your help Doug & Pat,

I have been able to incorporate both of your ideas into my code. I only have one slight problem with a compile error : Sub or function not defined.

This points at the following bit of code:

FindFirst "[StandardLocation]='" & Me!Location & "'"

It is the first & symbol that is giving me this error....
As I have just started programming myself, I don't really know what this error is about. Could someone give me a hand... thnx

Angelo

 

[Pat Hartman]

The FindFirst method is being interpreted as being a sub or function because it is not qualified with its object name. The code should be either,
rst.FindFirst or just .FindFirst if you've used the With as the posted example showed.

 

[D-Fresh]

Try building the criteria first...

dim strQuery as string

strQuery = "[StandardLocation]='" & Me!Location & "'"

.FindFirst strQuery

This should do it for you, and if not, you'll have the sting in a variable now, so you can debug better...


[This message has been edited by D-Fresh (edited 06-20-2001).]

 

[Angello Pimental]

Thanks both of you.

I have taken your advice, and now I get the error; Run-time Error 3251 : "Operation is not supported for this type of object"


Here is my code now:

Dim db As Database
Dim strlocation As String
Dim rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Standardlocationtable")
strlocation = "[StandardLocation]='" & Me!Location & "'"

With rst
.FindFirst strlocation ***This is where I get the error


Me!AdminContact = Me.Location.Column(2)
Me!BillingContact = Me.Location.Column(3)
'and so on

End With

I have a feeling that I get the problem due to how I am defining the string strlocation.

Thnx, Angelo

 

[D-Fresh]

Two things... First hover your mouse over the variable strLocation and see what that says.. Is the criteria correct???

Next, if you are going to use Pat Hartman's Method(which I advise you to use) you don't need the .findfirst call.... All the info is in the columns of your combo box already..

 

[Angello Pimental]

Thankyou Thankyou Thankyou....
It works!!!!!!!!!!!!!

Just one last question.... How do I get the information to appear automatically without selecting the text boxes?
And....
How do I get only the criteria that the user selects to show in the combo box, and not the other information??

Thnx again,
Angelo

 

[D-Fresh]

First, you should put the code in the AfterUpdate of the combobox... That way, as soon as something is selected, it will throw the data in the textboxes...

For the second question.. In design view, click on your combobox and go the the "Column Width" entry on the properties sheet... Then just fill in the width of your box and fill the other columns with zeros... Like so..

4;0;0;0;0;0;0

Put the number in whatever spot your bound column is, I'm assuming the zero spot in my example.. Also, put as many zeros as you have columns, minus the one you want to show...

 

[Angello Pimental]

Thank you Doug,
I put the code in the after_update properties but I still for some reason need to select the text box before the information will show....
Is there a refresh command I can use?

Thnx, Angelo

 

[D-Fresh]

me.refresh

But it really should be doing it automatically... When you click on a text box the info appears?

 

[Angello Pimental]

Thankyou the me.refresh works......

Thank you for all your assistance, you are an ACCESS GOD!!!!

Angelo

[This message has been edited by Angello Pimental (edited 06-20-2001).]

 

[dhoffman]

maybe I'm missing something as I know nothing about DAO and didn't read too closely, but why couldn't you do wha tyou were talking about simply by running a query after the combobox value is selected?