Access and lookups

[Dr Zoidberg]

Dim db As Database, rst As Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM Query_Name WHERE ParameterField = '" & YourFormControlName & "'")
me.customer_account_name.value = rst.fieldname
etc

In that piece of code i need to change......

Query_name to the name of my query - Done
Parameterfield to ?
YourFormControlName to the name of the combo box?
rst.fieldname to the field name in the query that i want copyed into the txt box.


so much easyer with a couple lookups in excel in my opinion.

 

[Dolph]

In that piece of code i need to change......

Query_name to the name of my query - Done
Parameterfield to ?
YourFormControlName to the name of the combo box?
rst.fieldname to the field name in the query that i want copyed into the txt box.


so much easyer with a couple lookups in excel in my opinion.

Parameterfield is the field name in the query you wish to sort on using the parameter from the combo box.

With rst.fieldname, you keep the rst. bit (because it's a variable that denotes the recordset returned from the query) and change fieldname to the field name from the query you wish to add into the box.

Like I said, it might alternatively be rst.fieldname.item, but the end result is the same, it's just a slightly different syntax.

As to it being done in excel being easier, it possibly is, at least for a simple system with no expansion, although I've always found access (and relational databases in general) much easier to work with than spreadsheets, especially if you want to do relational data management. The differences really become apparent when the system grows and changes, it's much easier to add an additional query to a database than to build an additional part of a spreadsheet to pull different data ime.

 

[Dr Zoidberg]

Than you very much for all your help on this Dolph, i would not have got to where i was with out it,
But i have found the solution to the last problem

I have set the number of columns correctly on the combo box for the number of columns of data i need copied out.

added this line of code and a few more like it to the on click event

Me!FieldToAutoFill = Me!ComboboxName.Column(1)

And all is good and working.

Once again thank you for the help Dolph, much appreciated

 

[Dolph]

No problem, glad you got it working. I'd avoided that solution because it's not that elegant in terms of future changes, but glad you got it sorted

 

[Dr Zoidberg]

No problem, glad you got it working. I'd avoided that solution because it's not that elegant in terms of future changes, but glad you got it sorted

Im sure its not the most elegant, but the amount of data in the table that is queried is small, and not something that will change much, The complete package is built on a modification of an existing file any way and i really don't like the way some of it is done, but to put it right would involve starting from scratch and that is not what was wanted for some reason - it had to look like the rest but do some thing a little different.