Add multiple records

[edster]

I have a form that adds an item into stock(every item has its own record), normally these are single one off items however now there will also be bulk orders of up to 25 identical items.

I would like a way to add the product infomation, add a new box to enter the number of items then press a button to automatically add that many records to the table.

What is the best way? Recordsets? Paste appends?

Many thanks

 

[DCrake]

Record set would be the easiest way but how is each part getting its PK and what method of stock control are you using? LIFO, FIFO,AVCO?

Also why not simply have a quantity field and use this to determine how many of the said items you have? Saves time and space and makes analysis easier.

David

 

[edster]

Hi David,

Its not stock control as such, just used that to try and explain the process. As you say in that case the product type wouls have a record not the items. They are specialised items which require individual tracking hence they receive Auto Ids on the individual records.

There are several details unique to each shipment, eg dates, lot IDs, recall codes, etc hence why there are currently entered individually however when we bring in 20 items together this info is the same for all items.

 

[DCrake]

You have 2 issues:
Are you picking the product to added from an existing product line?
Are you entering the product for the first time?

Which ever way you are doing this the basic of the code is as follows

Dim Rs As DAO.Recordset

Dim iCnt As Integer

Set Rs = CurrentDb.OpenRecordset("YourTableNameHere")


For I = 1 To iCnt
   Rs.AddNew
   Rs("YourFieldNamesHere") = YourValuesHere
   Rs.Update
Next I

Rs.Close
Set Rs = Nothing

Where iCnt is the number of new items being addedd to the table.


David

 

[wiklendt]

i think this is something i need for my database. i wanted to run this by the experts first:

i have a database of samples for work. every now and then, we run an antibiotic test on a sample. this data will need to be entered into the database. this test (called a 'phoenix') has two 'panels' of antibiotics that we can choose to run the samples against. they are for two different categories of bacteria: gram negative or gram positive.

when i run a phoenix on a sample, i want to select which panel i used, and to have a subform automatically add/append all the antiobiotics (about 20 antibiotics per panel) as a new record each.

so my vision of a working setup is (and please make suggestions if you think this is not the best way):

have a M-M table of antibiotic and panel.

have another results table.

have a result entry form: on the form, have a dropdown to select for either panel, which then triggers (in VBA? similar to the above posted code by Drake? or other?) to append from the M-M table (panel filtered by the combo box) to add each antibiotic into the results table (filtered by the form for each individual sample via a query that the form is based off), where i can then just add the results (instead of having to chose each antibiotic - which is ridiculous b/c they do not change from run to run).

does this sound like how some of you gurus might do it?

thx in advance.

 

[DCrake]

This sounds like the correct way to go. As you say the antibiotic doesn't change. So simply select a recordsetset based on the requirement and look though it adding the records to the table with the correct PK and FK for your M-M

If you code this into a function and call the function stating whether it is a pos or neg test the function will gather the data accordingly. Should you have any amendments you can do them in the function.

David

 

[gemma-the-husky]

marianne

if the antibiotic GROUP never changes, why don't you just store the group that you used. Why do you have to store all the indivdiual antibiotics? - i can understand you may need to do this, if the panel make up changes over time.

I think detailed stuff like this is never simple in terms of data structure - its similar in concept to a questionaire i think.

 

[wiklendt]

marianne

close! very close. it's actually spelt "agnieszka"

if the antibiotic GROUP never changes, why don't you just store the group that you used. Why do you have to store all the indivdiual antibiotics? - i can understand you may need to do this, if the panel make up changes over time.

the panel of antibiotics doesn't change, no, but the effect of each antibiotic on each bacterium does. so, the result for ampicillin in one bug will be different to the other bug. but every bug i test will have a result for ampicillin when i use that particular panel.

so, not "tested against all the antibiotics in panel NMIC/ID-101" but
"test against ampicillin resulted in MIC of >8 ug/mL, against tetracyclin (>=16 ug/mL), gentamicin (>8 ug/mL), levofloxacin (<1), nalidixic acid (=2) etc..."
until all the antibiotics & results for that panel are listed.... for each bug tested. (and obviously normalised, arranged to be efficient in an access table etc)

i am not wishing to store only what test was performed, but the RESULT of that test. each antibiotic in each panel is tested against each sample INDIVIDUALLY, not as a whole (i.e., the 20 antibitotics i mentioned that are in the panel are physically separated, they are not all applied at once to each sample - so there are 20 different results for each bug/panel combo, not just one), but what happens is that the sample is individually added to each well of the panel (containing a different antibiotic in each well) and the results are collected.

i know! i'll find a picture.... [bit of net surfing/googling] ....here is a picture of what a panel looks like: http://www.bd.com/ds/technicalCenter/brochures/br_222786.pdf

cover page of that pdf you'll see these cartridge thingys ("panel") with lots of round bits just sticking out ("wells").

each row of that panel has a different antibiotic, and each column changes the concentration of that antibiotic. you then decant a suspension of your bug into the panel and it distributes the bug to all of the wells via the force of gravity/capillary action.

this panel then goes into a big machine overnight (one machine has a carousel in it which can take uhm, up to about 100 panels, i think).

during that time, the bug either grows, if it's resistant to that particular antibiotic at that particular concentration, or is inhibited by that antibiotic all together, and does not grow at all.

one panel takes only one sample. it is also not reusable. so if i want to test 10 bugs, i have to set up 10 panels (where each panel has identical antibiotics and concentrations of those antibiotics).

the machine gives an output at which concentration the antibiotic inhibited the growth of the bug. so, isolate 14023e07 may have the result "<1 ug/mL" for ampicillin, while isolate 02045e01 may have ">8 ug/mL" for ampicillin.

this machine does not do long-term storage of results, but they can be printed and stored that way.

we have thousands of bugs, not all will be run through this test, but for the ones that are, i don't want to have to remember which antibiotics to add to my results table. while i may have a table with which antibiotics are on each panel, it would be easier if i could programmatically make the correct antibiotics jump into a subform for result entry (so, make ampicllin etc appear in the subform, with me then entering things like >8 or <1 in the result for that antibiotic).

also, it would be infinitely useful to then be able to query our data set to find all those bugs which are, say, resistant to ampicillin (this is determined by interpreting the raw data of ">8 ug/mL", which is detail in CLSI standards - though this is not the right forum to start talking about that!)

but yes, we also will have the benefit of having stored which bug have been tested. with so many bugs, it's important to see which ones have NOT yet been done either

hope that made things clearer, and not more muddy! sorry, i kind of waffled on a bit, but i love my job and just get excited to talk about it

I think detailed stuff like this is never simple in terms of data structure - its similar in concept to a questionaire i think.

tell me about it! i've been maintaining this inherited database for just over a year now. it's been a massive challenge but also very satisfying when i do things right my supervisor LOVED the efficiency created by this mysterious thing i called "normalisation", for example. and due to this fact alone, i have been heralded the access expert in our lab - go figure!!

 

[wiklendt]

I think detailed stuff like this is never simple in terms of data structure - its similar in concept to a questionaire i think.

Actually, maybe i can use the questionnaire analogy for why i need to store not just the panel, but each antibiotic result: a questionnaire is a single entity - a stack of sheets stapled together and EVERY person answers the SAME questionnaire.

now, if i wanted to store all the answers it would NOT be sufficient to just store which questionnaire each person did, but i would need to store ALL their answers to ALL the questions in the ONE UNCHANGING questionnaire. i might have two questionnaires, but they are each on a different topic, and each individual questionnaire doesn't change.

so rather than storing data that is useless for doing follow-up statistics:

John: questionnaire 1
Mary: questionnaire 1
Arnold: questionnaire 1
Betty: questionnaire 1
Betty: questionnaire 2
Bob: questionnaire 1

i want to store this:

John (questionnaire 1)
[answer to question 1 in questionnaire 1]: yes
[answer to question 2 in questionnaire 1]: maybe
[answer to question 3 in questionnaire 1]: unsure

Betty (questionnaire 1)
[answer to question 1 in questionnaire 1]: no
[answer to question 2 in questionnaire 1]: no
[answer to question 3 in questionnaire 1]: unsure

so as you can see, each PERSON will score differently even though they are answering the same questionnaire - which is why storing JUST the questionnaire is not sufficient. i want to analyse their answers and maybe draw conclusions about whether, for example, all females and no males answered "no" for question 1 in questionnaire 1, rather than only being able to say "all people answered questionnaire 1".

does that make more sense?