Writing a query or lookup into a form

[xsnd47]

I am still having problems getting my form to auto populate a single text box. I would like for a user to be able to enter a SKU or PO# and have description text box next to the SKU text box, auto populate with the text description of the SKU. I have come pretty close to getting it to work, but the data will not clear out for the next day's report, the data shows up as a table, or Access does not recognize what I am trying to link. I believe this can be solved with a the proper lookup expression, but I am not sure how to write it,

Can anyone help?

In essence...

SKU: 1234567 Description: ABCXYZ <Auto Populated from a pre-existing query I wrote.[/img]

 

[Andrew Fergus]

Hi, I think this problem is almost identical to your previous post.

I believe the issue is that you want to enter a product code into a form, the product description then shows beside the product code and cannot be edited or changed, and you then go on and key some additional data for this product - I'm assuming either sales or purchases or something similar. Is this correct? I'm also assuming that you want to store the data that has been entered into a table.

If so, this can definitely be done. In the absence of specific data I will provide a brief example of how it can be done and you may be able to apply the concept to your situtation.

Tbl_Products
Product_id (key)
Product_description

Tbl_Sales
Document_id (key)
Customer_id
Product_link (to link to tbl_Products)
Quantity

Click Tools -> Relationships
Add Tbl_Products and Add Tbl_Sales
Link the tables on the Product_id and Product_link (enforce integrity)

Qry_Sales
Add tbl_Products and tbl_Sales
Include Document_id, Customer_id, Product_link, Product_description (the only field to come from Tbl_Products), Quantity

Frm_Sales
Based on Qry_Sales
Build a combo box (look up values, from Tbl_Products, include id and description, don't hide key column, store product_id in product_link)
Include the other fields from the query (i.e. document, customer, description, quantity)
Select Product_description, Click View -> Properties -> Data -> Enabled -> No.

Save the form and give it a test drive. Provided you have set up a couple of products in tbl_Products you should be able to do what you asked, i.e. select or key a product into the form based on the products in tbl_Products and the description will show on the form.

This gives you the concept of how it can be done and I suggest you test this example before applying it to your situation.

HTH, Andrew.

 

[mdmilner]

I'm going to be a little more general & specific.
Explanation for Andrew.

Access allows you to build forms based on Tables & Queries. What this does is allow you to directly access table field values directly using built in methods. I didn't study Andrews post in detail, but that appears to me what he's suggesting.

And, it's an excellent method, easy to use and very versatile. In fact, you can usually use it instead of most other more complex methods as long as you plan what you need, well.
-
My interpretation of your problem sounds like you've done a proper 'lookup' of a table value but you're not doing it using the above method and it's not changing. One potential problem here could be as simple as the need to use this command.

Me.Requery (this requeries the recordsource for data)

In the below, I created a listbox that populates based on a query
I then set the default value based on a value in a table (tblDefaults)

To summarize, the Form_Open event triggers as the form opens.
The rowsource populates the list box but this event checks my table and selects for the user the last value used.

After_Update is an event placed on the list box that triggers after the user selects a new value. FindDefaults/SetDefaults are merely the procedures that change the values.

Private Sub Form_Open(Cancel As Integer)
strVal = FindDefaults("LastData")
Me.lboData.Value = strVal
End Sub
Private Sub lboData_AfterUpdate()
strVal = Me.lboData.Value
Call SetDefaults("LastData", strVal, 2)
End Sub

Public Function FindDefaults(ByVal MyDefaults As String)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * from tblDefaults", dbOpenSnapshot)

With rst
    .FindFirst "TypeOfDefault='" & MyDefaults & "'"
    If !TypeOfDefault = MyDefaults Then
        FindDefaults = !DefaultInfo
    Else
        MsgBox MyDefaults & " Information Not Found"
    End If
End With

Set rst = Nothing
Set dbs = Nothing
End Function

Public Function SetDefaults(MyDefaults As String, strEntry As String, _
                            strMod As Integer)
Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("Select * from tblDefaults", dbOpenDynaset)

     With rs
        .FindFirst "TypeOfDefault='" & MyDefaults & "'"
        If !TypeOfDefault = MyDefaults Then
           .Edit
           .Fields(strMod).Value = strEntry
           .Update
        Else         'If can not find it, create a new one
           .AddNew
           .Fields(1).Value = MyDefaults
           .Fields(strMod).Value = strEntry
           .Update
        End If
     End With

Set rs = Nothing
Set dbs = Nothing

End Function

 

[xsnd47]

Guys,
I started off with Andrew's suggestions and closed some matters up with mdmilner's suggestions. Thank you both very much. The form is operating exactly in the manner I wanted it to. Thank you.