SQL Select problem

[pbaldy]

Is this a continuous forms showing all the items, or some sort of unbound thing? If a bound continuous form, the code you posted might work. The form references would point to the selected record.

If not, I would probably pass the key field of the changed item, and update the table itself in the function rather than the form, then requery the form.

 

[Sevn]

I may have set this up wrong to begin with, and it's an unbound form that queries the table for source info to calculate on.

Here's what I have...
I have a tblItemDetails & tblCustDetails

• tblItemDetails has all Item Costs, based on which Plant they are made. Therefore; each Item/Plant combination has a unique ID (PID).
• tblCustDetails has all customer information, unique ID (ShipToNum).

The frmDataColl has dropdowns & text fields for data collection.

1. SalesRep enters Customer unique ID (ShipToNum), and the Customer Details populate the form AfterUpdate of ShipToNum.
2. SalesRep then selects a SKU & Plant on the form, enters Price, Freight, and a few other things. When any of these items are changed, I would like the module to fire, and recalculate the Margin%.
3. I'm hardcoding (physically creating on the form) 25 sets of input fields, so the SalesReps can enter up to 25 separate Items, Plants, Price, etc. If they don't use them, they will just be blank, and I won't have them carry over to the summary report I intend to create.
4. Currently; I was just going to create 25 separate modules, and name them RfshItemOne, RfshItemTwo, etc. I could then just point each set of input fields to run the corresponding module AfterUpdate.

Hopefully; this helps you see what I'm trying to do.

Edit: The two tables are not linked in any way, but the calculations are dependant on both, since we have customers in USA & Canada. So; if a USA Customer is getting items from a Canadian Plant, the calculation is different than if a Canadian customer is getting Items from a USA Plant.

 

[pbaldy]

I have to run out to meet my wife for lunch, but I certainly wouldn't create 25 modules. I'd create one that accepted the appropriate parameter(s), and have it update the info. I'll try to look at what you're doing in more detail when I get back.

 

[Sevn]

pbaldy,

Would it be easier, if I removed customer info, and sent you a copy of the DB?

 

[pbaldy]

Yes, you should be able to post it here. Leave some data in it we can test on, but of course it shouldn't be anything sensitive/private.

 

[Sevn]

Ok, here's a scaled down version.

The form will open automatically, and you can use the following inputs to see what's going on.

• Ship To: 501508
• Item SKU: 53850
• Branch/Plant: Eaton Warehouse (10903)
• FHC: DLT
• MOT: DMP
• Price: $3.00
• Annual Tons: 100
• Frt Rate per CWT: $1.00

The module isn't set to a button, so just open VBA editor, and run Module1 (Public Sub RfshItemOne()).

There is currently just one set of input boxes, which is good for 1 product. I will be replicating the them 24 times, so there will be a total of 25 items. I've included a screen shot of the form to explain.

 

[pbaldy]

Is there a reason this couldn't be a bound subform or something? I admit to not having looked closely enough at the sample to make that determination. In any case, one method based on how you're doing it might be to change the function to accept a line number:

Public Sub RfshItemOne(intCounter As Integer)

Then change the appropriate form references to use that line number:

If IsNull([Forms]![frmDataColl]("cboItemSKU" & intCounter)) Then

Then you'd call the function and pass it the line number:

Private Sub cboItemSKU2_AfterUpdate()
  RfshItemOne (2)
End Sub

 

[Sevn]

I like your int counter idea, and will try I tomorrow.

As for the bound subform...
I didn't learn Access the typical way, so I don't know some of the basic concepts. I started out writing short macros/scripts for Excel to get reports together for them, until the data got too big. About a year ago, I had to teach myself Access on the fly, so I could keep my job. Still learning, as you can see.

Honestly; without you guys help, I probably wouldn't still be working there (still a temp after 19 months). I've learned tons from here, and you guys make me look like a magician to the Directors of his company.

Thanks again for all your help, and I'll let you know how things turnout with the counter suggestion.