Auto populate new child records based on subform records

[robsworld78]

Hi, I have an form for placing orders, it has 4 subforms on it, 3 of which apply to this.

The first subform is for the order info, its tied to the main form via VendorID, it also has OrderID

The 2nd subform is for order details, inventory given on an order, its linked to the order info subform via OrderID. It also has a ProductID.

The 3rd subform is inventory remaining for the vendor the order is being placed for. Its linked to the main form via VendorID. It also has ProductID which is common to the 2nd subform and a way to tie them together, hopefully.

What I need is when a new order is being placed the child records on the "order details" subform will auto populate to whatever product is showing on the "inventory remaining" subform.

If I add

Me.Product2 = Forms!CreateOrders!VendorInventoryLevelSubform.For m!Product

to the on click event on the new order detail subform it will populate with the first record on the "remaining inventory subform" however it uses the same record on that form for all new product given.

I tried creating a recordset between the 2 subforms with productID and another with product name but neither worked because no records exist on the first subform. I thought it might after I add items but it broke and gave debug error. The only idea doing this was to try and link them so I could use the above code "on click" event and have someone just click the new item row and it populates that would be easier then it currently is. Ultimately it would be nice to have it look like the middle subform on the picture I attached when the form opens except with a 0 in the given.



As I mentioned I attached a picture of the form and some notes on it explaining better. If you have any ideas on how I should tackle they would be appreciated.

[June7]

I would have to analyze the project if you want to provide it by attach to post.

[robsworld78]

Thanks, the form you want to look at is called "createorders" If you open from the dashboard select "Cyndy Roberts" as there's remaining inventory for her. If you open the form from the navigation pane enter "11" when it asks for name.

When you open the form Cyndy's Inventory remaining should show up where you select product for the new order for every product she has in her bin, disabled until a "Date Out" is chosen on the order info as it currently is.

[robsworld78]

Hi, did you get a chance to look at the db?

[June7]

Yes, I have been looking at it and trying to understand the data flow and form behaviors, although set aside for a few days. This project is soooo familiar! Those full screen forms still give me issues when trying to debug. Keeps me from interacting with other apps and sometimes can't see all the controls on my laptop screen. When the CreateOrder form opened I could see Cancel Order button but not the others and not all the header controls would show. I commented out all the Maximize VBA code and set CreateOrder form and the subforms to not fit to screen. I set project to overlapping windows instead of tabbed documents and resized forms so I can see all the controls.

Now trying to understand what you want to happen. Might take me a while.

What I need is when a new order is being placed the child records on the "order details" subform will auto populate to whatever product is showing on the "inventory remaining" subform.

In other words, you want a set of rows in 'order details' for all the products listed in 'inventory remaining'? This means creating records in a table. Probably need to use an INSERT SELECT sql action statement. These records would have to go into Inventory table?

[robsworld78]

Ah crap, I didn't think of that, the db is meant to run on 1920 x 1080 screen resolution, sorry about all the trouble that would have been. I think if you add scroll bars to the form you can scroll around to see everything.

You do understand correctly, the inventory table is the one that needs the records equivalent to the "inventory remaining" subform in the middle, don't need the amount remaining, just the product names.

Thanks!

[June7]

VBA code:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunSQL "INSERT INTO Inventory (Product, OrderID) " & _
"SELECT ProductID, " & [Forms]![CreateOrders]![OrdersOrdersSubform]![OrderID] & " AS OrderID " & _
"FROM VendorInventoryLevel " & _
"WHERE (BarsLeft>0 Or BarsLeft<0) AND VendorID=" & [Forms]![CreateOrders]![VendorID] & ";"
DoCmd.SetWarnings True

The trick to this will be figuring out what event to put this in. I tested it in the DateOut textbox AfterUpdate. Might also need code that prevents repeated writing of records if user changes date. Maybe don't let them change date by disabling the date textbox? Would have to be coded in the control getting focus after the date textbox.

Scroll bars don't show up on the dashboard forms.

[robsworld78]

Thanks, I'll try this tomorrow night and see what happens. If the date needs to be disabled to make it easier that's no problem, the order will always be for the day its made, I have another system for early orders, its the pre-order form which is for approving pre-orders which are done on the vendors side.

There's actually another file, its what the vendors use for doing there thing. I attached it if you want to take a look at it. To get into an account if the name is a first name followed by ? then the first name is the password. The other accounts passwords are in the vendors table. This ones pretty simple, just a login dashboard, a registration form and vendors dashboard for there thing, the night count which you helped me with before, which works great by the way and a form for doing pre-orders. The attachment also has the new backend file which the vendor side needs, I added another table and a bunch more fields for vendors for the application form.

This one fits on 1600 x 1025, very strange the scroll bars don't show, they do on all the other forms and the dashboard is nothing special, maybe because its set in the db options as the opening form?

There's a lot of hands on an order, there's a computer for an inventory controller, this person makes orders and edits them if there's errors, then there's a computer for a cashier who only gets the order when its ready to pay and soon will be able to change certain things on an order, like license fees and rates anything to do with money but nothing for inventory.

Once an order is created it can be seen on the vendors computer so they can enter how many they have left when they get back, after they do that on there computer they can't see the order again and it goes to the inventory person for review, after he reviews it and is happy he posts it and it goes to cashier, after cashier takes payment its marked paid. It stays in that status for 24 hrs then the inventory person opens a form which allows them to go through all the paid orders easily and check which should be marked complete and once thats done the order is finally gone and can't be altered. Up to that point it can be reset to be edited again.

With all that and the system being able to track inventory for a single trailer and as many freezers, bins, vans and vendors as you want it took a lot of time to think out and at some points I didn't think I could do it. If I had to start over I wouldn't know where to start.

Its very nice of you to help me out here, these golden bits of code you've given me are helping me understand things better and I'm able to re-use them. The code you did for the night count I was able to change it a bit and also use it for a cancel button which sets all barsold back to 0 for the order. If your code works for this one there's so many applications I'll be able to use it for. I'll let you know how I make out, thanks!

[robsworld78]

Just going over the code to try and make sense of it and I love the fact that you have 3 or 4 ID's in there, gives me a tingling feeling.

[June7]

If the order is always for the current day, why have user select date at all? Just use current date as default with Date() function. Lock the box so date can't be changed. Have to find different event to run the code, possibly the open event of the main form.

One line of code forgot to include:
Forms!CreateOrders!OrdersInventorySubform.Requery

[robsworld78]

Just ran the code and it works perfectly, you're good! If a person hits cancel it still deletes the order it just created, perfect! I put it in the after update on the freezer combo box, since thats where the inventory is coming from it makes sense it fills it at point. I don't understand the date issue, the 3 orders I placed all went good and I changed the date on 2 of them.

I will lock the date though, it makes sense, one less field to fill for the user.

Thanks for the help, I wish I could buy you a drink, cheers!

[June7]

The date field would be an issue only if that was the control event you put the code in. Since you didn't that is not the concern. What happens if user changes the selection in the freezer combobox? Will another full set of the records write to table? Consider putting the code in Load, Open, or Current event of the form.

[robsworld78]

Now I understand what you're talking about, of course it would duplicate if you select another date or freezer if the code is in the after update because I'm updating that field again, I didn't get that before. I went through a bunch of events and it wouldn't work on any subform unless it was in an after update so for now what I did was stuck with the freezer because there is only 1 freezer right now and will be for some time I'm sure but to be safe I added "enabled = false" after the update so a person can only select it once. If another freezer gets added and they selected the wrong freezer they can cancel and start over, nothing was entered on the order at that point anyways so no loss. Plus I like the effect when you select a freezer and it populates the subform, looks like its really doing something.