By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,154 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,154 IT Pros & Developers. It's quick & easy.

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

jhowells28
P: 8
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,
May 17 '07 #1
Share this Question
Share on Google+
11 Replies


puppydogbuddy
Expert 100+
P: 1,923
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.
May 18 '07 #2

jhowells28
P: 8
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!
May 18 '07 #3

puppydogbuddy
Expert 100+
P: 1,923
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]
May 18 '07 #4

jhowells28
P: 8
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]
Jul 25 '07 #5

puppydogbuddy
Expert 100+
P: 1,923
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
Jul 25 '07 #6

jhowells28
P: 8
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
Jul 25 '07 #7

puppydogbuddy
Expert 100+
P: 1,923
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
Jul 25 '07 #8

jhowells28
P: 8
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.
Jul 26 '07 #9

puppydogbuddy
Expert 100+
P: 1,923
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub sbfPurchaseOrderNumber_AfterUpdate()
  2.  
  3. If Not IsNull(Dlookup(“[ItemNum]”, “tblPlanning”, “tblPlanning.[PurchaseOrderNum] = ‘” & Me![sbfPurchaseOrderNumber] & “’”)) = True Then
  4.  
  5. Me![sbfProductID].Value = Dlookup(“[ItemNum]”, “tblPlanning”, “tblPlanning.[PurchaseOrderNum] = ‘” & Me![sbfPurchaseOrderNumber] & “’”)
  6.  
  7. End If
  8. End Sub
  9.  
Jul 26 '07 #10

jhowells28
P: 8
This just never worked for me. I appreciate your help.
Mar 10 '08 #11

puppydogbuddy
Expert 100+
P: 1,923
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.
Mar 10 '08 #12

Post your reply

Sign in to post your reply or Sign up for a free account.