CHALLENGE! Dynamic/custom Data Based off a Textbox

[tsukaska]

Hi,

I have a calibration database I'm creating. I need a way to do dynamic/custom/changeable data entry. I would like to be able to type the calibration points in a textbox of some sort, (or click a button and enter the points) such as: 1,2,200 and then have those datapoints added as fields in my table tblCalibration (and check if the field already exists and regardless if it's new or not, pull the requested fields into the current form frmCalibration).

Each datapoint would need to generate two fields. For 1, it would need to generate the field 1GramAsFound and 1GramAsLeft. I would probably want to add a radio button to select the units so it could be named 1KgAsFound if needed. I would also need to be able to specify how many decimals and the accuracy of the item calibrated. See the attached image for an example of what I want to be able to do (this comes from a company website).

That's the best I could come up with for a 'dynamic' table creator/editor. Other ideas or suggestions are welcome!

 

[The_Doc_Man]

Are you seriously suggesting to dynamically add fields to a table or do you mean that you wish to add records with some variability of field format/content to the table?

The former is absolutely fraught with peril because you will bloat the heck out of your tables in a quick heartbeat and end up with fields that are terribly under-utilized. The latter might be easier with a little bit of design work and some skull-sweat ahead of time.

 

[tsukaska]

Yes, dynamically add fields to a table. Otherwise there's no point to an access database for me. I do weight calibrations and the weights used vary from scale to scale and also according to the scale's accuracy. Currently I have 20 different weights and I could need to use any number of combinations of them for any given scale depending on what the customer requests. It seems like Word, Excel, and mail-merge would be the best routes then...

 

[CJ_London]

Otherwise there's no point to an access database for me. I do weight calibrations and the weights used vary from scale to scale and also according to the scale's accuracy

just out of curiosity - were you planning to have a separate table for each scale as well?

 

[tsukaska]

Of course not, that would be silly! I've been tinkering around with access for a month or two now and I picked up the gist of relational databases fairly quickly (although I'm sure the basics could always be better learned and reinforced and I'm always open to learning). I had a table tblInventory for all of the inventory items with a lookup combobox to relate to the company which had that item, and different tables for different lookup combobox elements such as Manufacturer, Model, received condition, cost, etc. I had the forms set up nicely so that it would add a new Manufacturer and Model to their respective tables from the form's combobox... -sigh-

 

[CJ_London]

so if I have understood you correctly rather than having a table

item..wt1...wt2...wt3 etc
1234..1........2......3......
3456..1........1......1......

have one?
item...wtno...value
1234..wt1.....1
1234..wt2.....2
1234..wt3.....3
3456..wt1.....1
3456..wt2.....1
3456..wt3.....1

which is the normalised way

 

[tsukaska]

Yes!!! That is what I was trying to figure out! Curse the many years of ingrained Excel thinking! Haha. Thank you thank you! And as a bonus, at least I now have a crude excel database backup if I ever need it!

 

[CJ_London]

OK - so back to Access then! To answer this question

I would like to be able to type the calibration points in a textbox of some sort, (or click a button and enter the points) such as: 1,2,200

In the after update event of the textbox, or a click event on a button you would run some code along these lines

 dim vals() as integer
 dim I as integer
  
 vals=split(txtcals,",")
 for I=0 to ubound(vals)-1
     currentdb.execute("INSERT INTO tblCals (item,wtno) VALUES(" & me.itemno & ", " & vals(i) & ")")
 next i

you would need some protection against inserting twice and being able to manage the fields (correction for typos, deleting unrequired records, etc)