Populating unbound form from VBA

[doma23]

I was populating forms quite simple before I normalized my tables. I've had this:

Client_____Country___Period___Prod1___Prod2___Prod 32
Microsoft__Germany___ 2011 ___ 45 ___ 60 ___ 85

Client, Country and Period would give unique record, so I would use something like this code:

rs.Open "tblData", cn, adOpenStatic, adLockOptimistic
sCriteria = "(([ClientID]='" & Form_frmMain.cmbClient & "') AND (" & "[CountryID]='" & Form_frmMain.cmbCountry & "') AND (" & "[Period]='" & Form_frmMain.cmbPeriod & "'))"
rs.Filter = sCriteria
 
Form_frmMain.txtProduct1 = rs![Product1]
Form_frmMain.txtProduct2 = rs![Product2]
...
...
Form_frmMain.txtProduct32 = rs![Product32]

And that worked fine, but after normalization, I have this table:

ClientID__CountryID__Period__ProductID__Sales
___1_________2_______2011_______1_______ 45
___1_________2_______2011_______2_______ 60
...
...
___1_________2_______2011_______32______ 60

So now my code from above can't work, because Client, Country and Period will not give unique combination.
I would need to filter it further by the Product to find the unique record.

But since there is let's say 32 products, I would need to use the filter criteria 32 times (for each product once), instead of just once for all products like I was doing before.
I suppose it would have significant influence on execution speed as well?

The second problem is that now I have ProductID which I need to match, but on the form I have text labels that show which product it is and textboxes that represent revenue for each of these products.
So, how do I match the ProductID in the table and Product shown on form?

rs.Filter = "(([ClientID]='" & Form_frmMain.cmbClient & "') " & _
"AND (" & "[CountryID]='" & Form_frmMain.cmbCountry & "') " & _
"AND (" & "[Period]='" & Form_frmMain.cmbPeriod & "'))" & _
"AND (" & "[ProductID]='" & Form_frmMain.? & "'))"

Obviously with the comboxes I guess I can do it (not tested yet) as they are bound in row source to ID value, but they show text description. But how to do it with the ProductsID column when all I have on the form is label showing text description of the product and textbox showing the value of sales for that product?

I hope I made myself clear to some degree.

So what is the right way to do this?

BTW. I know I could use DLOOKUP to populate it, but I've read somwehere it's not the best way since it has effect on the speed of execution so much.

Thanks!

[sierra7]

Hi,
You cannot use Unbound forms in this senario, other than to display only one record at a time.Your data will now appear as a list down the page. You cannot spread it accross the page like you might have done with 'repeat fields' before normalising.

The best you might do is to create say three or four subforms side-by-side. The first would filter for ProductID 1-10, the second 11-20 etc. The main form would hold Company, Country & Period. Each of the subforms would have to link through these three fields.

Strictly speaking your data is still not Normalised.
S7

[doma23]

Well, not necessarily. Like I've already mentioned, I can just use different filter criteria in VBA 32 times (to match the number of products), and that works, tested.

Here is the sample code (observe bolded code):

If DCount("*", "[tblRevenues]", _
    "(([ClientID]=" & Form_frmMain.cmbClient & ") AND (" & "[CountryID]=" & Form_frmMain.cmbCountry & ") AND (" & "[Period]='" & Form_frmMain.cmbPeriod & "') AND (" & "[ProductID]=1))") > 0 Then
    rs.Filter = "( ([ClientID]=" & Form_frmMain.cmbClient & ") AND (" & "[CountryID]=" & Form_frmMain.cmbCountry & ") AND (" & "[Period]=" & Form_frmMain.cmbPeriod & ") AND (" & "[ProductID]='1') )"
     Form_frmMain.txtProduct1 = rs![Revenue]
End If
 
If DCount("*", "[tblRevenues]", _
    "(([ClientID]=" & Form_frmMain.cmbClient & ") AND (" & "[CountryID]=" & Form_frmMain.cmbCountry & ") AND (" & "[Period]='" & Form_frmMain.cmbPeriod & "') AND (" & "[ProductID]=2))") > 0 Then
    rs.Filter = "( ([ClientID]=" & Form_frmMain.cmbClient & ") AND (" & "[CountryID]=" & Form_frmMain.cmbCountry & ") AND (" & "[Period]=" & Form_frmMain.cmbPeriod & ") AND (" & "[ProductID]='2') )"
     Form_frmMain.txtProduct2 = rs![Revenue]
End If
 

And now, I do this for another 30 times.
DCount is being used in case there is no data for specific Company, Country, Period and Product. So only textboxes with Products that have the value in the database will be populated.
I would use similar logic when writing procedure for saving modified and new data.

The code can be probably further optimized by using loop to save the data about whether records exist in variables and then use another loop to assign the value to the fields. I might think about that later, depending on whether I will have the time to do it. (I'm under time constrain to finish the tool).

I'm still wondering though if there is more efficient way to do this, as I would like to now how to properly manipulate normalized data.
My code might work fine in the end, but it doesn't really look proper to me.

BTW. I have one frmMain, on which man can find comboboxes Client, Country and Period, and then I have ona tab control, which have DATA tab and REPORTING tab.
On DATA tab I have all the textboxes for the various products revenues. So I'm populating these textboxes when user wants to view/modify data, and when user press SAVE button, I need to save the modified data or add the new data in the database (depending on whether the record exists or not).

I really don't understand how would creating three or four subforms help me? That would change only visual layout of the data on the form.

And why do you think it's still not normalized?

[NeoPa]

I don't want to get too deeply involved with this as there seem to be far too many ideas here that I would not expect to see in a properly defined database. I'm sorry if that sounds harsh Doma. I only posted because you seemed under the impression you had normalised your database. In case it helps see Database Normalisation and Table structures.

In Access it is generally a sign of something wrong with the database design if someone asks for help populating a form. It's the reverse of the way Access is designed to work. It's possible to do, and many people fall into that trap when they come from other programming environments, but it's like owning a luxury car and having the chauffer drive it beside you while you walk.

As I said, I don't want to get involved deeply here, but I felt I had at least to try to warn you away from some of what you're doing. I'll leave it with you.

[doma23]

The thing is NeoPa, that I've read quite some about normalization, and the whole time I was under impression that I'm doing things by the book.
I designed the tables and made the relationships, and I gave it quite some thought in an effort to do it properly and normalize it.
And now in the last couple of days I'm trying to build the form and the queries to manipulate these data in a way that the users will expect to do it, and I find it quite complicated, not impossible, but very complex.
And I just don't know if I did something wrong, or if it's the nature of the data.

NeoPa, I would really appreciate if you could help me here man. I know it would take you some time, and I'm willing to pay you something for your effort if needed.
If you agree I can even send you the whole database, so you can see the design of the tables and the relationships among them.
I really think it wouldn't take you much time to understand it as the project is quite simple IMO.
I'm doing this project for the company I work for, and I know they don't care if it's normalized or not as long as it works. But I do, and I so badly want to understand it, for myself. I will not have peace until I do so. :(
Please let me know.

Btw. here is the relationship view, if you would just take a look I would really be happy.
I'll answer whatever questions you may have.
P.S. CategoryID and ProductLineID are duplicated on purpose, because I need to preserve these information, as they will change over time.*

[NeoPa]

I expect you completely underestimate what it is you're asking for here Doma, but I like your attitude.

I will send a PM with some details and we can possibly chat at least. You can send me the db too if you like. This is too complicated to do via a forum page but if we get to talk it will help to have the existing project in front of me. You will need to follow the guidance in this link though to ensure all is done properly (Attach Database (or other work)).

I checked your profile, and I hope your English is as good as most of the Germans I speak with, as my German is less than rudimentary.