Need help auto entering data into a subform.

[torb123]

I have created a database to track customers, orders, invoicing, etc. I need help with the orders subform (order details). My table structure is customers (one) to orders (many), orders (one) to order details (many), and products (one) to order details (many). The order details table has each line item for the order including quantity and price charged, etc.

To speed up the process of entering orders I would like to make it so that products could be auto entered into the subform. For instance, one of our orders might be a chimney, which has many different parts (or products). In stead of going through and trying to find every little piece that goes with the order I would like it to auto enter off of a dropdown menu.

For instance if I were to pick “standard 12’ chimney” out of the drop down list it would enter all of the products and quantities for that order automatically. Right now I have it set where I made bogus orders for the chimney and just copy and paste the information from that subform to the new order subform.

I was wondering if it is possible to get it to automatically copy and paste those records (from one subform to the other) without having to open the fake order and copy stuff, then close it and start a new order to paste it into manually like I need to do now. I also thought of making another table that has a relationship to products (most likely many to many) so that I could have “standard 12’ chimney” related to all of the individual products that makes up the chimney. That way I could choose it out of the dropdown and it would query the table and find out what products and quantity go along with it and enter it into the subform for the order that is currently open.

I consider myself beginner/intermediate level of access programming
I don’t know if either of these are possible or of there is a better way to do it. If you could please help, or point me in the right direction I would appreciate it. And if you need more information let me know.

Thank you.

 

[Spikemannen]

How about setting up question that gets all the products that is needed for the different kinds of chimneys.
It may be alot of work in the beginning.

If every product has a "indicator" that it belongs to a certain chimney type the question should be fairly easy to built.

 

[willir]

torb123

To "autoenter" data as described, you need to setup "profiles" for your components.

Basically, you have an Order Entry system, but in addition to entering products, you want to "profiles" which create your entries.

The Order Entry system is fairly standard...

tblCustomer
CustomerID - primary key
CustomerName
Address
...etc

tblProducts
ProductID - primary key (or ProductCode)
ProductName
Units
DefaultProductPrice
...etc

tblOrder
OrderID - primary key
CustomerID - foreign key to tblCustomer
OrderDate
BillToAddress
ShipToAddress
...etc

tblOrderDetail
OrderDetailID - primary key
OrderID - foreign key to tblOrder table
ProductID - fireogn key to tblProduct tab;e
OrderQuantity
OrderPrice
...etc

Here is what is different...
tblProductTemplate
TemplateID - primary key
ProductMasterName

tblProductTemplateDetail
TemplateDetailID - primary key
TemplateID - foreign key to tblProductTemplate
ProductID - foreign key to tblProduct
OrderQuantity

Then you need two things...
- An unbound combo box on the main form based on tblOrder along with a commit button. End user selects a profile, and commits it.
- Coding behind the commit button that copies / writes data to the OrderDetail table based on the selected template. Something like...

Dim rstO as DAO.Recordset, rstT as DAO.Recordset
Dim strSQL as String

If Nz(Me.cmbSelectTemplate, 0) Then
   if Nz(Me.OrderID, 0) Then
      strSQL = "SELECT * from tblProductTemplateDetail" _
      & " WHERE TemplateID  = " & Me.cmbSelectTemplate
      Set rstT = CurrentDB.OpenRecrodset(strSQL)
      Set rstO = CurrentDB.OpenRecordset("tblOrderDetail")

      With rstT
         .MoveFirst
         Do While Not .EOF
            rstO.AddNew
            rstO!OrderID = Me.OrderID
            rstO!ProductID = !ProductID
            rstO!OrderQuantity = !OrderQuantity
            rstO!ProductPrice = Nz(DLookup("DefaultProductPrice", _
            & "tblProduct", "ProductID = " & !ProductID), 0)
            rstO.Update
            .MoveNext
         Loop
      .Close
      rstO.Close

      End With

      Set rstT = Nothing
      Set rstO = Nothing

End If

Hopefully, you can see that the template is used to populate the order detail. By-the-way, you will have to requery the subform after the above, Me.YourSubForm.Requery to see the recently created records.

Richard

 

[torb123]

Thanks, I will give it a try.

 

[torb123]

I appreciate your help willir, but I am having a compile error when I run the code at the line:
"Set rstT = CurrentDB.OpenRecrodset(strSQL)"

I think it is with the .OpenRecordset Method. The error is, "Compile Error, method or datamember not found" I checked online about the error, and it said to make sure you have Microsoft DAO 3.0 or higher checked. I have it checked it is "Microsoft DAO 2.5/3.5 compatible library". Any thoughts that may be causing the error?

Also, the code is red indicating a problem with this code:
" rstO!ProductPrice = Nz(DLookup("DefaultProductPrice", _
& "tblProduct", "ProductID = " & !ProductID), 0)"

I understand the relationships and the tables and they are all set up. I also added the two new tables. I am fairley new to the code. I can see what it does, but not much beyond that, or how to fix it.

I too had a question about the unbound combo box. You said to base it on tblOrder, did you mean you mean tblProductTemplate? If you did mean tblOrder, I don't fully understand it.

Thank you again for all of your help.

 

[willir]

Check the SQL sentence...
strSQL = "SELECT * from tblProductTemplateDetail" _ & " WHERE TemplateID = " & Me.cmbSelectTemplate


Can see an obvious error. Check and make sure the names aer correct. Also, it the TemplateID is a text string instead of numeric, you have to encapsulate the value within quotes.

You can use the immdeidate window / debugger to help you.

1) Generate the error
2) Open the immediate window using CTRL-G
3) Print the SQL statement by typing the following...
?strSQL
4) Copy the results into the clipboard
5) Open up the Query builder -- "Create query in Design view" and close the window dialog box when prompted for the table to use for the query.
6) Switch from the GUI design view to SQL view. (From the menu, "View" -> "SQL View"
7) Paste the SQL statement from your clipboard, and make sure the end of the line is terminated with a semi-colon, ";".
8) Run the query. The query builder will high-light your error.

My fault on the syntax error -- drop the ampersand on the second line...

            rstO!ProductPrice = Nz(DLookup("DefaultProductPrice", _
            "tblProduct", "ProductID = " & !ProductID), 0)

Richard

 

[torb123]

Thanks a lot Richard, I will give it a try on Monday and let you know how it goes.

 

[torb123]

I tried using the immediate window, but when I put in ?strSQL, it doesn't bring up anything, when I hit enter it just puts in a blank line.

I then made a query to pull the records. The query works fine, but it will not work when I put the code into the VBA editor. The code I took from the query and am trying to use now is,

"strSQL = "SELECT ProductTemplateDetail.*, ProductTemplateDetail.TemplateID"

strSQL = strSQL & "FROM ProductTemplateDetail WHERE (((ProductTemplateDetail.TemplateID)=[forms]![Orders]![cmbSelectTemplate]));"

I still get the same error. In the line, "Set rstT = CurrentDb.OpenRecrodset(strSQL)" it highlights the ".OpenRecrodset" of it saying that the method or data member can not be found.

Any thoughts? or reasons the immediate window doesn't bring up anything when I type ?strSQL

Thanks

 

[PHV]

Try to use a real 3.x DAO library instead of the 2.5/3.5 compatible one.
And then tweak a little your sql code building:
strSQL = "SELECT ProductTemplateDetail.*, ProductTemplateDetail.TemplateID"
strSQL = strSQL & " FROM ProductTemplateDetail WHERE TemplateID=" & Forms!Orders!cmbSelectTemplate

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

 

[torb123]

I was trying to type DAO.Recordset, but it wouldn't come up with just recordset as a dropdown option. The only option with recordset in it was dao.RecordsetOptionEnum. Would that have anything to do with the error? Should I change dao.recordset to dao.RecordsetOptionEnum?

I have Microsoft DAO 3.6 Object Library.

 

[torb123]

Thanks for cleaning up my code PH, I just copied it (for the most part) from the SQL mode of the query because I don't really know the language. I still have the same error though.

 

[PHV]

I have Microsoft DAO 3.6 Object Library
So tick it and uncheck the 2.5/3.5 compatible.

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

 

[torb123]

I had changed it from 2.5/3.5 compatible to Microsoft DAO 3.6 Object Library at the end of last week, but it didn't help with the error.

Thanks

 

[torb123]

Ok, so I am the biggest Idiot. I had it typed as recrodset instead of recordset. Thank you all for the help. I am now havnig a little trouble bringing in the price, but I should be able to figure it out.

Thanks again,

Eric