Access and lookups

Dr Zoidberg · 10 Sep 2008 at 18:09

Hi

I have an access file that i am having a few problems with,

A form has a drop down menu on it, i need the contents of that menu to be the contents of a column from another table,
I cant get it to work.

Once that is down i need to move to the next step,

When the user selects a value in the drop down menu the 4 text fields under it need to fill with the relevant info from the next columns in the table the drop menu relates to.

Im sure i could do this in excel but no idea where to look in access.

Thanks
Keith

Dr Zoidberg · 10 Sep 2008 at 18:40

I now have the drop down menu populated correctly i just need to make the text fields below it fill in now if any one can help.

Thanks

Dolph · 10 Sep 2008 at 18:57

Dr Zoidberg said:
I now have the drop down menu populated correctly i just need to make the text fields below it fill in now if any one can help.

Thanks

Easiest way is to use an 'on-click' event to update the values in the text boxes. but it depends exactly what you want to do with the data afterwards as to the best way of achieving it.

Dr Zoidberg · 10 Sep 2008 at 19:03

Dolph said:
Easiest way is to use an 'on-click' event to update the values in the text boxes. but it depends exactly what you want to do with the data afterwards as to the best way of achieving it.

Once the form is filled out, including these boxes, the contents of the form are saved as a new record in a table.
I will have a play with the on-click event and see where it takes me,
Thanks

Dolph · 10 Sep 2008 at 19:11

Is the value in the lookup box unique? (ie each entry in the dropdown box only represents one entry in the table) If so, the easiest way to do it is to create a query containing the fields from the table you wish to use, and in the field that corresponds to the drop down box, use the expression builder to make the 'where' field link to the dropdown box on the form.

Then use an 'on-click' event in the combo box to set the value property of the text boxes to the corresponding field in the query using simple code.

This allows you to populate the text boxes with data from one query, while having the control source (which should be the table that's eventually going to hold the new data) set to the new field in the new table.

Dr Zoidberg · 10 Sep 2008 at 19:28

Dolph said:
Is the value in the lookup box unique? (ie each entry in the dropdown box only represents one entry in the table)

Yes each value in the drop down box is unique and only represents one entry in the table it is gained from.

Dolph said:
If so, the easiest way to do it is to create a query containing the fields from the table you wish to use, and in the field that corresponds to the drop down box, use the expression builder to make the 'where' field link to the dropdown box on the form.

OK, i created a query containing all the fields from the table that are needed (there is a few that are not needed so i left them out)
In the field that corresponds to the drop down box - i cant find a 'Where' field !!!!!

Will move on to this once i find a 'Whre' field

Dolph said:
Then use an 'on-click' event in the combo box to set the value property of the text boxes to the corresponding field in the query using simple code.

This allows you to populate the text boxes with data from one query, while having the control source (which should be the table that's eventually going to hold the new data) set to the new field in the new table.

Thanks

Dolph · 10 Sep 2008 at 20:12

I'm getting my SQL confused with my access. It's the criteria field in the query builder.

Dr Zoidberg · 10 Sep 2008 at 21:18

Well i think i am taking 3 steps forward then 2 steps back, but slowly getting to where i need to be.

Thanks for the help

Dolph · 10 Sep 2008 at 21:24

Dr Zoidberg said:
Well i think i am taking 3 steps forward then 2 steps back, but slowly getting to where i need to be.

Thanks for the help

No problem, happy to help. If you need help with anything else, feel free to yell

Dr Zoidberg · 10 Sep 2008 at 21:47

Dolph said:
No problem, happy to help. If you need help with anything else, feel free to yell

Well that simple code in the on click event needed to populate the 'other' fields from the query results is being a little bit of a pain, keep thinking i have it then it throws an error

Dolph · 10 Sep 2008 at 22:41

Dr Zoidberg said:
Well that simple code in the on click event needed to populate the 'other' fields from the query results is being a little bit of a pain, keep thinking i have it then it throws an error

What code have you got at the moment?

Dr Zoidberg · 10 Sep 2008 at 22:46

Dolph said:
What code have you got at the moment?

Tried all sorts of different bits and bobs,

came across something with google that i modified to this

Me.Customer_Account_Name = Customer_Query.Column(1)

as part of a VBA sub in the onclick event but its not happy

Dr Zoidberg · 10 Sep 2008 at 22:57

I have found a lot about using the SetValue action in a macro but there is no setvalue action available to me in the macro builder !!

Dolph · 10 Sep 2008 at 23:07

Dr Zoidberg said:
Tried all sorts of different bits and bobs,

came across something with google that i modified to this

Me.Customer_Account_Name = Customer_Query.Column(1)

as part of a VBA sub in the onclick event but its not happy

try me.customer_account_name.value = Customer_query.column(1)

Dr Zoidberg · 10 Sep 2008 at 23:22

Dolph said:
try me.customer_account_name.value = Customer_query.column(1)

customer_account_name is the name of the first text field i want filled in automatically.

I am putting the code in the onclick event for the combo box, that the right place?

with or with out .value i am getting

runtime error 424
object required

Dr Zoidberg · 10 Sep 2008 at 23:31

It is down to the way the query column is being referenced in the code,

if i swap out customer_query.column(1) for = combobox1

then the text field fills with the value selected in the combo box

So i am now playing with the reference to the query.

Dolph · 10 Sep 2008 at 23:38

I think it's because the query hasn't actually been run.

as part of the on click, before you try to set the values, run the query.

Code:

currentdb.execute "your queryname here", dbfailonerror

That should populate the query correctly so you can then reference it.

Sorry, that's my fault, I should have remembered that.

Dr Zoidberg · 11 Sep 2008 at 00:08

Dolph said:
I think it's because the query hasn't actually been run.

as part of the on click, before you try to set the values, run the query.

Code:

currentdb.execute "your queryname here", dbfailonerror

That should populate the query correctly so you can then reference it.

Sorry, that's my fault, I should have remembered that.

That's giving me a cannot execute a select query

I now know why i don't use Access much

Dr Zoidberg · 11 Sep 2008 at 00:16

Quoting out all the code, Opening the form and selecting an option in the combo box then opening the query, the query is holding all the correct info, checked for all options in the combo box.

So that is working ok, but i cant then get the info from that query into the form.

Dolph · 11 Sep 2008 at 00:50

I forgot the .execute plan only works for action queries *faceplam*

you could call it as a recordset, and reference that, it means changing the query slightly and passing the parameter to it via the code.

Code:

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

It's been a while since I've used that approach though, so i may have the syntax wrong for the recordset. it might be rst.field.name, but my brain is letting me down (it's easier to do it than to cover it for someone else)