Add a suggested value from a related table to txt field in subform.

[jhowells28]

I have a subform for data entry purposes and I would like to populate a text box with a "suggested" value that will be pulled from a related table. I say "suggested" because the information stored in the related table is 90% accurate, but is not always correct. It would be pulling in the Product ID associated with a Purchase Order ID. If the Product ID pulled in is incorrect, however, I would like to be able to override it.

I am using Access 2000.

Can anyone help with this?

Thanks,

[puppydogbuddy]

More information is needed, but based on what you have provided so far, there appears to be at least 2 ways it can be done.

1. You could set the default value property of the SuggestedValue textbox to be equal to the table value of the source field via a DLookup.
2. You could code the form's before update event procedure to do the DLookup before the form is updated.

We could assist you more if you would provide the relevant form names, table names, control names, more details about the table and fields used as the basis for the SuggestedValue.

[jhowells28]

More information is needed, but based on what you have provided so far, there appears to be at least 2 ways it can be done.

1. You could set the default value property of the SuggestedValue textbox to be equal to the table value of the source field via a DLookup.
2. You could code the form's before update event procedure to do the DLookup before the form is updated.

We could assist you more if you would provide the relevant form names, table names, control names, more details about the table and fields used as the basis for the SuggestedValue.

I have a habit of providing too much information, but here goes...

The tables are tblInvoices, tblPurchaseOrders, and tblPlanning. tblInvoices has a one to many enforced relationship with the tblPurchaseOrders table. tblPlanning is a production plan so it has every work order that is planned with it's corresponding item ordered. This table changes as orders are completed or filled, so I can't enforce referential integrity between tblPlanning and tblPurchaseOrders.

I have created a form based on the tblInvoices table, [frmInvoiceEntry], and added a subform, [Invoices subform], to frmInvoiceEntry. The subform is in a datasheet entry format. When the user enters the purchase order number in the first field of the subform, I want the form to automatically suggest the item ordered. The purchase order entered will not always have a corresponding purchase order in tblPlanning, so I don't want an error to appear when it doesn't work out.

If I use the DLookup function, can you illustrate what it should look like?

Thanks!

[puppydogbuddy]

This illustrates the use of the DLookup. You need to place the code behind your subform as code that will execute after the PO has been entered and updated.
Replace the object names I used with the actual object name in your database. The syntax used assumes that PO is a text data type.

In words the code below says if the PO entered matches a PO in tblPlanning, then the SuggestedValue control on the form is equal to the $ amount shown for that PO in the table. This amount placed by code in the SuggestedValue control can be overwritten.

[font=Times New Roman][size=3]Private Sub PO_AfterUpdate()[/size][/font]

[font=Times New Roman]If Not IsNull(Dlookup(“[PO]”, “tblPlanning”, “tblPlanning.[PO] = ‘” & Me![PO] & “’”) = True Then[/font]

[font=Times New Roman] Me![SuggestedValue].Value = Dlookup(“[POAmt]”, “tblPlanning”, “tblPlanning.[PO] = ‘” & Me![PO] & “’”) [/font]

[font=Times New Roman][size=3]End If[/size][/font]

[font=Times New Roman][size=3]End Sub[/size][/font]

[jhowells28]

I think I have confused you. I am not trying to get a suggested value as in a $ value, but rather just the item that was ordered. If I have an order for a car part, for example, I want to be able to enter the PO and automatically have the associated part on that order to come in as a suggested value. "Suggested" meaning I want the user to have power to override if it is incorrect or not available.

I tried the code that you gave me and I couldn't get it to work. Can you clean out the font code and resend?

This illustrates the use of the DLookup. You need to place the code behind your subform as code that will execute after the PO has been entered and updated.
Replace the object names I used with the actual object name in your database. The syntax used assumes that PO is a text data type.

In words the code below says if the PO entered matches a PO in tblPlanning, then the SuggestedValue control on the form is equal to the $ amount shown for that PO in the table. This amount placed by code in the SuggestedValue control can be overwritten.

[font=Times New Roman][size=3]Private Sub PO_AfterUpdate()[/size][/font]

[font=Times New Roman]If Not IsNull(Dlookup(“[PO]”, “tblPlanning”, “tblPlanning.[PO] = ‘” & Me![PO] & “’”) = True Then[/font]

[font=Times New Roman] Me![SuggestedValue].Value = Dlookup(“[POAmt]”, “tblPlanning”, “tblPlanning.[PO] = ‘” & Me![PO] & “’”) [/font]

[font=Times New Roman][size=3]End If[/size][/font]

[font=Times New Roman][size=3]End Sub[/size][/font]

[puppydogbuddy]

sure, sorry about the font codes..at the time I posted I did not know how to turn the font settings off: See the example without the font codes below:

Private Sub PO_AfterUpdate()

If Not IsNull(Dlookup(“[PO]”, “tblPlanning”, “tblPlanning.[PO] = ‘” & Me![PO] & “’”) = True Then

Me![SuggestedValue].Value = Dlookup(“[POAmt]”, “tblPlanning”, “tblPlanning.[PO] = ‘” & Me![PO] & “’”)

End If

[jhowells28]

I can't get this to work. I am confused #1. What is [PO] referring to? The field in the form or the field in tblPlanning or both? #2. I don't understand what field Me! [SuggestedValue].Value is referring to.

Let me explain everything more clearly. tblPlanning contains two fields: PurchaseOrderNum and ItemNum. My form is a data entry form. The order of the columns is sbfPurchaseOrderNumber, sbfProductID, sbfCostperUnit, sbfQuantity, and sbfValue (Calculated field). When a user is entering this data, I would like sbfProductID to show a suggested item number that is pulled from tblPlanning. I want the user to be able to enter to accept or type over the suggested item number if needed. This data entry writes information to tblInvoices. There is a relationship between tblInvoices and tblPlanning, but since items continually change in tblPlanning, no referential integrity can be enforced.

Please let me know if you need any more information. I really appreciate your help!

sure, sorry about the font codes..at the time I posted I did not know how to turn the font settings off: See the example without the font codes below:

Private Sub PO_AfterUpdate()

If Not IsNull(Dlookup(“[PO]”, “tblPlanning”, “tblPlanning.[PO] = ‘” & Me![PO] & “’”) = True Then

Me![SuggestedValue].Value = Dlookup(“[POAmt]”, “tblPlanning”, “tblPlanning.[PO] = ‘” & Me![PO] & “’”)

End If

[puppydogbuddy]

I can't get this to work. I am confused #1. What is [PO] referring to? The field in the form or the field in tblPlanning or both? #2. I don't understand what field Me! [SuggestedValue].Value is referring to.

Let me explain everything more clearly. tblPlanning contains two fields: PurchaseOrderNum and ItemNum. My form is a data entry form. The order of the columns is sbfPurchaseOrderNumber, sbfProductID, sbfCostperUnit, sbfQuantity, and sbfValue (Calculated field). When a user is entering this data, I would like sbfProductID to show a suggested item number that is pulled from tblPlanning. I want the user to be able to enter to accept or type over the suggested item number if needed. This data entry writes information to tblInvoices. There is a relationship between tblInvoices and tblPlanning, but since items continually change in tblPlanning, no referential integrity can be enforced.

Please let me know if you need any more information. I really appreciate your help!

Now that you have provided me the field names, and other info that you did not provide before, I have revised the DLookup, accordingly: Let me know right away if it is not working the way you want it to.

__________________________________________________ ____________
Try placing this code behind your data entry subform
RE: Me>>>>if focus is on the main form, me is a quick reference to the main form
>>>>>>>>>if focus is on the subform, me refers to the subform
Me.Parent>>>>focus is on the subform, but you want to reference an object on the form

Essentially, the procedure below says “ after entering the order # on the subform, check for and see if an itemNum exists for the order # in tblPlanning. If it does, then copy the suggested itemNum in tblPlanning to sbfProductID.
__________________________________________________ ______________

Private Sub sbfPurchaseOrderNumber_AfterUpdate()

If Not IsNull(Dlookup(“[ItemNum]”, “tblPlanning”, “tblPlanning.[ PurchaseOrderNum] = ‘” & Me![sbfPurchaseOrderNumber] & “’”) = True Then

Me![sbfProductID].Value = Dlookup(“[ItemNum]”, “tblPlanning”, “tblPlanning.[ PurchaseOrderNum] = ‘” & Me![sbfPurchaseOrderNumber] & “’”)

End If

[jhowells28]

I put the code in, however, I get an error that says:

Compile Error:

Expected: list separator or )

I don't know what this means. Apparently I'm missing a braket or something, but I copied your code and pasted it in.

[puppydogbuddy]

I put the code in, however, I get an error that says:

Compile Error:

Expected: list separator or )

I don't know what this means. Apparently I'm missing a braket or something, but I copied your code and pasted it in.

Try it now:

Private Sub sbfPurchaseOrderNumber_AfterUpdate()
 
If Not IsNull(Dlookup(“[ItemNum]”, “tblPlanning”, “tblPlanning.[PurchaseOrderNum] = ‘” & Me![sbfPurchaseOrderNumber] & “’”)) = True Then
 
Me![sbfProductID].Value = Dlookup(“[ItemNum]”, “tblPlanning”, “tblPlanning.[PurchaseOrderNum] = ‘” & Me![sbfPurchaseOrderNumber] & “’”)
 
End If
End Sub
 

[jhowells28]

This just never worked for me. I appreciate your help.

[puppydogbuddy]

This just never worked for me. I appreciate your help.

Why did wait until now to respond? Your last response was July 2007. If you had gotten back to me sooner, I could have fixed the syntax problem. You need to understand that site experts are not at your computer, so we are dependent on feedback from you to present the problem correctly and tell us what is happening when you implement our suggestions.