How to automagically fill in a field based on another one

[Mmmel]

Hi again!

This forum has been FABULOUS so far, so I thought I'd throw up my latest stumper. I know that this issue has been discussed out the wazoo but I can't find anything quite like my issue.

I have a form that is based on a table, "T: Financials - Data Entry". It allows me to enter in cheque information for depositing. I need to track the cheques by category and subcategory. So, the Category field on this form looks up the category from a different table, called "T: Cat and Sub Categories Assigned" that contains both the category and a subcategory. I have and Autonumber Primary Key field assigned in this table called "ID". I have it set up so that the combo box displays both the category and subcategory and, when I choose it, dumps only the category into that field (this is working well). What I want it to do is go next door to the Sub-category field and dump the subcategory that was associated with the category into it all by itself.

I figured I would put a DLookup into the After Update event of the Category field, but I'm having trouble with the syntax. This is what I have so far:

Private Sub Combo44_AfterUpdate()
    Dlookup("[Category]","T:Financials - Cat and Sub Categories Assigned", "Sub-category =" & ID =
End Sub

Obviously, its not done. Can you give me some pointers?

THANKS in advance!

[Mr Key]

See the Attachment below

See the attached database below for Clarifications!
In this database if you Enter "CheksA" at [Catg] the Access will Completes the rest of the field by using MACROS "Setvalue" and if you Enter "ChecksBBBB " at [Catg] then you will see autocompletes at [Cat1] and [Cat2]
I hope this will give a hint of what you might be starting with, unless you have any doubt

See the attachment here Autocompletes.zip

[Mmmel]

Thanks very much! I really appreciate your time!!! But, I cannot use or open the macro...it tells me it's not valid.

Also, my situation differs from the one you set up since you're only referencing one table. I'm actually looking up the category and subcategory from a different table and storing it with the main table.

Any other ideas that anyone could try?

[Mr Key]

We normaly use set value no matter the source table are, you select SETVALUE macro and decide where do you want your source from and destination, SETVALUE macro are among types of macro which considered to be UNSAFE and therefore you have to enable the content to view the Macro

[patjones]

Generally the way people use DLookup is to assign the result to something else. For instance, var = DLookup( ... ). Also, the right hand side of your DLookup is missing, so I'm not sure if you are completing the line correctly.

Pat

[Mmmel]

Thanks, Pat!

I know that I'm missing some of the RHS of my Dlookup; I don't know how to complete it correctly. Can I put the field that I'm trying to assign the value to in where you have "var"? Or, is that for global variables only?

[patjones]

Are you simply attempting to assign the value to a control on the form, or to a column in the table?

If you're trying to do the assignment to a column in a table, you'd need to attach the table to a recordset in the code first, then use the DLookup function to assign the value to the appropriate member of the recordset.

In terms of the DLookup function itself, take a look at the Access help pages. I think you should attempt to build a solution to this first and then we can see what problems, if any, come out of that. Thanks.

Pat

[NeoPa]

Mmmel:
What I want it to do is go next door to the Sub-category field and dump the subcategory that was associated with the category into it all by itself.

Do you actually select the category or the sub-category from your ComboBox? If it is the latter then this can be extracted directly from the ComboBox itself, without any need for cumbersome DLookup() calls.