Another DLookup Question

[wlwoman]

I have a form containing many fields. I would like the form field "Production Approver" to be filled in from the tableProdApprTable. I have experimented (unsuccessfully) using the following DLookup function:

DLookUp("ProdApprSig","ProdApprTable","ProdApprCode = " & [ProdApprCode])

The function itself works in a separate query and a separate form but I can't figure out how to include it in this form.

Can I trigger a query using [Enter ProdApprID] in a separate (non-visible) field called ProdApprNumber?

The Objective

The objective is to fill in a signature field on the form based on individual codes that will be assigned to each potential approver. This will suffice for an electronic signature.

There are 3 types of approvers, each with their own DLookup function in working order - outside the main form!

Am I thinking 'right' about this? If not, how else could I reach the objective? Writing macros is not my forte, although it might be a better avenue.

Any help would be greatly appreciated!

 

[fredk]

If you simply want to populate one field in the form by looking up a value based on another field that the user will enter (which is what I think you basically want to do) than you can use a macro -

Create a macro:

Use the "setvalue" action - in the item, enter the name of the field to be looked up - and in the expression, enter the dlookup function (I am trying to give you an example to follow but you are probably better off looking up dlookup in help and following the syntax

DLookUp("[field to look up]","[table to look up]","[table to look up]![field that matches]=[Forms]![your form name]![field on form that matches]&quot

Then, in the after update event of the field that the user will enter the data, insert the macro name.

Hope this helps.

Fred

 

[wlwoman]

My macro looks like this:

DLookUp("[ProdApprCode]","[ProdApprTable]","[ProdApprTable]![ProdApprSig]=[Forms]![tblNCMRForm]![ProductionApprover]&quot

The macro is ok as far as code - the idea was to put it in the AfterUpdate of the ProdAppr# field on the form so when the user types the passcode in the ProdAppr# field, it will update the ProductionApprover field with the correct signature.

1.My ProdApprTable has 2 fields: ProdApprCode and ProdApprSig
2.My form tblNCMRForm has a field ProdAppr# and neighboring field ProductionApprover
3. I called up the macro in the AfterUpdate of the ProdAppr# field.
4. Nothing happens.......

What have I done wrong?

 

[quest4]

wlwoman, I am doing a similar dbase with ECN's. I have dozen or so approved by textbaoxes and date apprved texboxes. I added a CmdButton and labeled it approved and put one next to each approved textbox. In the tblAutorizedUsers table I added checkboxes for each approval that needed to be done. Then in the OnClick event of the CmdButton I put this code:
Private Sub CCDApproved_Click()
On Error GoTo Err_CCDApproved_Click
If DCount("[LogOn]", "tblAuthorizedUsers", "[LogOn]='" & fOSUserName() & "'&quot = 0 Then
MsgBox "You Are Not an Authorized User."
Me!CCDApproved.SetFocus
'Re-Enter Approval, same field
Else
'Valid user test, Is a valid user
If DCount("[LogOn]", "tblAuthorizedUsers", "[LogOn]='" & fOSUserName & "' And [ApdCCD] = True&quot = 0 Then
MsgBox "You Are Not Authorized To Approve This Section."
Me!CCDApproved.SetFocus
'Re-Enter Approval, same field
Else
'The User is Authorized
Me.CCDApprovedBy = fOSUserName
'Enter LogOn in Approved By textbox
Me.CCDDate = Date
'Enter Today's Date in the Date textbox
Me.CCDComments.SetFocus
'Set Focus on Comment textbox
End If
End If
Exit_CCDApproved_Click:
Exit Sub
Err_CCDApproved_Click:
MsgBox Err.Number & " " & Err.Description
Resume Exit_CCDApproved_Click
End Sub
This will verify that the logon user is in the authorized user table and then it will check to see if he can approve that section and if so, it will put the logon name in the approved textbox and todays date in the date aproved textbox and set your focus to the comments testbox. It works really well. You will have to change the name of the tables and the field name also, and add checkbaoxes but then it works. Hope this helps you out. Good luck with your dbase.