Lookup fields in a form 1

[gk7828]

Hi

I am new to Access although I used it breifly about 5 years ago.

I am trying to recall data of upto 5 fields in a form ie

Date, ProjectNo, AccountNo, Customer, Comments

I have tried the Combo Wizard but can only get 1 field to display.

I am starting to travel in circles please help

gk7828

 

[JRMS]

dhookom,

Thanks for your help. I have created two subforms. The top form will create display only and not store. The contents of the top subform is as follow:

System (*************) <=== combo box
--------------------------------------
System ID (********) <==== autofill from selection above
Location (********) <==== autofill from selection above

The second combo box will be a both. Below is an example

Policy (***********) <=== Combo box
Policy Details (********) <=== Autofill

Comments (***********) <==== Input from user and saved for reporting.

Thanks for your help.

 

[dhookom]

If you only want to display and would like to use the original solution then go back and read my previous posts particularly 26 Oct 06 15:02.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jsteph]

I think a pseudocode overview of what's needed here is this:

Combobox source: SELECT TblTanf.[System Name (As In TAFT)] FROM TblTanf ORDER BY [System Name (As In TAFT)];

Fields to autofill would occur in the AfterUpdate of the combobox:
(this assumes, regardless of whether it's a new record or existing, that you want to autofill/upodate the fields upon combobox selection)

Private Sub Combobox_AfterUpdate()
dim rs as dao.recordset,sq as string
sq = "Select Field1,Field2 From tblTANF Where System Name = '" & me.Combobox1 & "'"
set rs = currentdb.openrecordset(sq,dbOpenSNapshot)
if rs.eof and rs.bof then
    'this would be strange
else
    'obviously not the same physical db fields--this is just for clarity
    me!field1 = rs!field1     
    me!field2 = rs!field2
end if
end sub

Now, this assumes that SystemName is uniuqe, if it's really SystemID, then use that in the combo but also display the name. Also, there are many ways to make that more efficient--other ways to skin this cat, but I think this may be the gist of what you want.
--Jim

 

[dhookom]

If:
1) as stated, the values are to be displayed only and
2) [System], [System ID], and [Location] are all fields in the table [System Name (As In TAFT)] then
no code is required to make this work.

JRMS has never told us where the two extra values are coming from or significan information about tables and fields.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[jsteph]

dhookom,
Yes that's true. The multi-field combobox solution would do the same as what I'd suggested, and it would require less db io. But as I'd said, there are many ways to skin this cat. My preference is always to make the combobox simpler--I can never keep the darn Lbound of the combobox straight--columns(0) is BoundColumn 1...then tyring to remember if it's columns(3) that was CustName, or was it Address...I'm getting too old for thinking that hard .

Anyway, assuming it's a local lookup table, as these combos often are, then I tend to favor a simpler combobox in favor of saving a fetch to the db to lookup the values from the same table. But alternatively I've opened the rs as a module-level recordset variable, and did a FindFirst for the lookup.
--Jim