How to load all Product ID's by default

[Rangerguy2000]

Hi, How can I make a subfrm load all the Product ID's by default when I open it and I just have to input the quantity ordered.

[June7]

You use code to batch create records in table. Do you have Orders and OrderDetails tables?

Something like:

CurrentDb.Execute "INSERT INTO OrderDetails(OrderNum, Product, OrderDate) SELECT " & Me.OrderNum & " As ON, ProductID, Date() AS OD FROM Products"

The tricky part is probably committing the Orders record first and grabbing that record's primary key to save as foreign key in OrderDetails.

[Rangerguy2000]

I have a OrderDetails table that is recording all the data correctly right now but my issue is I want the Product_ID's to load by default when I open the frmMain.

I have the following:
frmMain that has Customer Details.
ProductsTable that has Product_ID's, Names etc.
frmSub that pulls the Product_ID's from the ProductsTable but it is manual entry, I have to specify the Product_ID every time and then update the quantity.
I want the frmSub to pull all the Product_ID's from the ProductsTable and their relevant quantity if already ordered from the OrderDetails table

[June7]

Conventional structure is: tblCustomers, tblProducts, tblOrders, tblOrderDetails.

Whatever your db structure, the suggested method still applies, adapt it.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Rangerguy2000]

Just dropped a file.
Pardon me, I used the analogy of Products & Orders but it is really for Drivers & Metrics.
I used P&O to make it simpler.

[June7]

Simplest is to use your real model and not cause confusion with dummy examples. However, as stated, adapt:

CurrentDb.Execute "INSERT INTO tbl3_MetricDetails(Record_Date, Driver_ID, Metric_ID, Flag_ID) SELECT Date(), '" & Me.cboDriver_ID & "' AS DID, Metric_ID, 1 AS FID FROM tbl4_MetricIDs"

Then requery the subform to display the new records.

Me.frmSub_MetricDetails.Requery

Might want to set the date textbox DefaultValue property with Date()

[Rangerguy2000]

Thanks, that worked but I still need to work some kinks.
The table is auto-populating itself with multiple entries. I guess I have to write some Iif statements for it to look for variables.
Will update in a few hours from home.

[orange]

Rangerguy2000,

How about writing a 5-6 line description of what your database represents --plain English, no jargon?
Readers find it easier to offer advice/suggestions once they understand WHAT you are trying to do.

Good luck.

[Rangerguy2000]

If you had read the complete post, you would've understood. Take some patience to read & write I guess.

[Rangerguy2000]

OK this isn't working. My ask is;
When I load the frmMain with the inputs Driver_ID and Date; the frmSub should show me all the Metric_ID's and then only inputs I have to make is the Flag_ID's.

Also the frmSub should update based on new Driver_ID and Date.

Any help is really appreciated.

[davegri]

Take some patience to read & write I guess.

Maybe you like to guess, but we don't.
When we ask for more information, it's to understand how to help you.

[June7]

Why isn't it working - error message, wrong results, nothing happens?

Provide the attempted code.

Having the records created automatically when form opens to existing records can cause duplication. Use code to prevent.

Perhaps code should be in the date textbox AfterUpdate event.

[Rangerguy2000]

1st of all I need to make the mainFrm Driver_ID field as working drop-down (Combobox) work.
Currently what is happening is, I can only scroll from 1 driver to the next using the next or back built-in access functions.
as soon as I update a driver with the metrics and want to select the next driver using the Driver_ID drop-dow, access tells me;

"The changes you requested to the table were not successful because they would created duplicate values in the index, primary key, or relationship."

[Rangerguy2000]

Any help is really appreciated on how to make the dropdown list work.

[June7]

The combobox works exactly as supposed to. Issue is with your data structure and form design.

tbl2_Drivers should have only 1 record for each driver - driver should be unique. You have designed this data entry so that selecting driver from combobox edits record or attempts to create new record and results in duplication.

If you want this combobox to work as a navigation aid, then it should be UNBOUND. Bind a textbox to the Driver_ID field to display this value but don't allow edits in that control.

I find that the compound key and indexing is causing issues. Also Flag_ID is set as required. I removed the compound key and compound index and set Flag_ID as not required. The INSERT SELECT then works.

However, the subform query uses INNER JOIN and needs to be RIGHT JOIN. Probably should set Metric_ID and Metric_Name controls as Locked Yes, TabStop No.

Private Sub Record_Date_AfterUpdate()
If DCount("*", "tbl3_MetricDetails", "Driver_ID='" & Me.Driver_ID & "' AND Record_Date=#" & Me.Record_Date & "#") = 0 Then
    CurrentDb.Execute "INSERT INTO tbl3_MetricDetails(Record_Date, Driver_ID, Metric_ID) SELECT #" & Me.Record_Date & "# AS RD, '" & Me.cboDriver_ID & "' AS DID, Metric_ID FROM tbl4_MetricIDs"
    Me.frmSub_MetricDetails.Requery
End If
End Sub