Form fill in 2

[a1fireman]

I want to use a form key to read a table and fill in selected data items on the form view.

For example I enter on the Error-data form an ID Number and from the signed contact table fill in the Company's Name, Address, City, and State into the Error-data form.

I thought that I found something in DLookUp but it fails everytime I enter an ID and hit enter.

Using a different computer language I would have just moved the key and read the table until I matched them moved each field I wanted into the form and displayed it.

Any help will be appreciated,
Frank

 

[Remou]

What did you use for DLookUp?

It is also possible to use a recordset.

 

[a1fireman]

This is what I have coded, I got this layout from another web site. I though I had it working but I closed down my Access session and lost it and when I tried to find the sample code again I could not so I had to go from memory and it has not worked again.

=================================================
Private Sub TIN_BeforeUpdate(Cancel As Integer)

Dim varcontractnumber, vartin, varname, varcontactname As Variant
vartin = DLookup("[tin]", "signedfi", "[signedfi] ='tin'")
varcontractnumber = DLookup("contractnumber", "signedfi", "signedfi =[contractnumber] ")
varname = DLookup("financial Institution", "signedfi", "signedfi = [name] ")
varcontactname = DLookup("contactname", "signedfi", "signedfi = [contact] ")
If (Not IsNull(vartin)) Then Me![TIN] = vartin
If (Not IsNull(varcontractnumber)) Then Me![BOC] = varcontractnumber
If (Not IsNull(varname)) Then Me![Financial Institution] = varname
If (Not IsNull(varcontactname)) Then Me![Contact] = varcontactname

End Sub
===========================================

Frank

 

[Remou]

I think that moving the quotes will sort out the problem. It may be better to use the After Update event.

The general idea is:
[tt]DlookUp("[FieldName]","[TableName]","[IDFieldName]=" & Me.IDControlName)[/tt]
Where IDFieldName is numeric.

[tt]DlookUp("[FieldName]","[TableName]","[IDFieldName]='" & Me.IDControlName & "'")[/tt]
Where IDFieldName is text.

[tt]DlookUp("[FieldName]","[TableName]","[IDFieldName]=#" & Me.IDControlName & "#")[/tt]
Where IDFieldName is a date.

 

[a1fireman]

I tried setting up my DlookUp statements according to your samples but it still does not work. Here is what I get:

Run-Time error '64479':
The expression you entered as a query parameter produced this error: 'The object doesn't contain the Automation object 'sighedfi."

When I select Debug it takes me to this statement:

vartin = DLookip("(tin)", "[signedfi]", "signedfi = '[tin]'")

I have to admit I know just enough VBA to get myself into trouble and some times it even works after I hack on it for a while. COBOL is so much nicer.

If you don't want to be an accessory to the fact and not want to help me then I'll under stand, so in advance I want to thank you for your help it has been appreciated.

Have a nice day.

 

[PHV]

vartin = DLookip("(tin)", "[signedfi]", "signedfi = '[tin]'")
Here the the explanation of your code:
You have a table named signedfi.
This table contains at least 2 fields named (tin) and signedfi.
You want to retrieve the value of the (tin) field where the signedfi field is equal to the literal value [tin].

You really wanted that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[a1fireman]

No that's not what I want, I'm getting confused.

OK, I have a table called signedfi, within that table I have 12 fields of them are TIN (this is the KEY), Contract Number, Financial Institution, and Contact. I have a form, in that form I have a field called TIN I want to take that ID and go to signedfi table and pull the three items I need to fill in the form. The IF statements are what I need. Oh I see that I don't need the first one I already have my TIN number.

Sounds easy that is why I'm getting frustrated about how hard it is to get accomplished.

Thank you PHV for your help.

 

[PHV]

So, reread carefully Remou's excellent explanation posted 9 Oct 06 16:44 and feel free to play with the F1 key.