0

I have a client that needs to give out pricing reports while being able to save multiple versions of data in a report. EX: One SKU is only for one vendor. (So, I need to make a report that has all the same data as the others + this one SKU) Each vendor may have items at different markup percentages. (I need a report that can be saved where only these items are different markup %'s) Each vendor could have differing markups (3 vendors will be at 20%, 1 vendor at 15%, etc.)

Obviously I can't have them changing each item record in their item table data every time they have to send out reports. But they need to be updateable if they need to make changes.

I can't decide the best way to go about this. Maybe I'm missing something obvious. Thoughts?

bmurray959
  • 11
  • 3
  • 1
    To report data, it really needs to be in a record in table. Not knowing your data structure, hard to say which. Might be able to accomplish with either subreports or UNION query. – June7 Aug 30 '22 at 02:33
  • Goes SKU, model, retail $, percentage markup, wholesale $. Some vendors get some percentages and some have multiple percentages in each report. I'm tempted to just copy the whole table tbh, and say here you go. Then I'd have to make them edit form controls and that would be a mess. – bmurray959 Aug 30 '22 at 02:37
  • Unfortunately, in a multiple simultaneous users split database, users could conflict in selecting records that way. Unless by copy you meant a copy in the frontend. – June7 Aug 30 '22 at 02:39
  • I know. I only have two end users, so it'd be less of an issue, but still a problem. I could make a bunch of fields with checkboxes I guess and have them mark which report they go on, but they'd still have to add a query and change the report record source if they needed a new one. – bmurray959 Aug 30 '22 at 02:44
  • Only two users? Then could have a check field for each of them. OpenReport code could apply filter on appropriate field depending on user. It's a bizarre brainstorm but you sound desperate. – June7 Aug 30 '22 at 03:00

1 Answers1

0

First, use reports for displaying data, particularly, printing. Edit & search the data with forms. In this case, first have the user open a form instead of the report.

why you might need a report with parameters in access: https://www.codevba.com/msaccess/docmd_openreport.htm#.Yw3DrNPMLb0

In this loading form have a combobox where the user can select a SKU and a button they can click to open the report. In the button click event open the report while passing the selected SKU to the report (see the many ways below or check out most of the links).

Here are a couple links to get started:

https://www.youtube.com/watch?v=bdmCmYYETac

https://learn.microsoft.com/en-us/office/vba/api/access.form.openargs

Once you have the selectedSKU in the report you just set the appropriate control to a function of selectedSKU that calculates the price: https://support.microsoft.com/en-us/office/create-a-calculated-control-08e19416-0026-4d78-8bea-f6b8ff9570a7

If you happened to set the control to a public function like GetPriceFromITEM&SKU( ItemID as long, SKU as long) that would be best as you could use the function elsewhere and access reports can bug over complicated expressions. there are many ways to pass parameters like SKU around a database. For instance, locally, when the form button is clicked you can call Docmd.OpenReport with WhereCondition and or OpenArgs constructed from the form settings. But since you can refer to almost any public object you can omit those settings open the report invisibly and then set the report controls from the form controls and then make the report visible.

More globally you might have a settings table with a row for currentSKU then when the user changes SKU in the form SKU combobox afterUpdate event call a public function like SetCurrentSKU and in the report have the SKU Control set to GetCurrentSKU. Or have the controls set and get from the settings table directly

mazoula
  • 1,221
  • 2
  • 11
  • 20