Updating 100 records in a table using a single Form Entry

[jamie23]

I Work for an NHS organisation.

A department here orders prescriptions and sends them out to Gp Surgeries. I have been tasked in designing a database to make this easier for them.

Prescriptions arrive on a Pallet. Each pallet has 100 Boxes of prescriptions on it. Each box Contains 2000 Individual prescriptions.

I have a table which contains the following:

Box Number
Serial Start Number
Serial End Number

When the user enters the first box number and the serial start number, i need it to calculate the serial end number and insert another 99 rows into the table and also calculate the serial start and end numbers for these other 99 records.

Any help would be most greatful

 

[jdraw]

Start with writing in 4 or 5 sentences a description of the process you are trying to support. Identify the things involved.

See this tutorial as an example of the process, work through it, then use the process with your own data/situation.

Do NOT jump into the nitty-gritty of Access before getting a clear model of your data (tables and relationships).

 

[jamie23]

Thanks for the prompt response.

We want to log all of the boxes of prescriptions into a table using one entry into a form. Currently, the user does this in Excel as follows.

Eg.
A B C
1 Box Number - Serial Start Number - Serial End Number
2 190456 - 00000001 - 00002000

The user would enter the first box number on the pallet, first serial number on the prescription in that box and also the serial number of the last prescription in that box. The user then hits ENTER and it populates 100 rows of data, relating to the 100 boxes on the pallet. Formulae is used to work it out.

E.g

A B C
1 Box Number - Serial Start Number - Serial End Number
2 190456 - 00000001 - 00002000
3 190457 - 00002001 - 00004000
4 190458 - 00004001 - 00006000
5 190459 - 00006001 - 00008000

And so on until 100 rows are populated. This then logs all of the boxes on the pallet with the serial numbers of the prescriptions.

The box number always increments by 1. The Serial Start number is always one more than the previous End Number and the Serial End Number is always 1999 more than that boxes particular Serial Start Number.

Hope this helps.

Thanks

 

[Chrisopia]

I would approach it using:

For i = 1 To 100
'code
Next i

This creates 100 instances of i and repeats the code using the variable. Coupled with INSERT SQL queries, it should be doable...

You could, for example, in an onclick event: (untested)

Dim BN As Long 'Box Number
Dim TheSQL As String 'Insert Query
Dim SS As Long 'Serial Start
Dim SE As Long 'Serial End

'Add the first row based on the entry on the form
BN = Me.BoxNumber 'the box number from the form
SS = Me.SerialStart 'the start number from the form
SE = SS + 1999 'counts 1999 e.g. SS = 1, SE = 1+1999 = 2000

TheSQL = "INSERT INTO tblBoxes ([Box Number],SerialStart,SerialEnd)" _
             & "VALUES (" & BN & ", " & SS & ", " & SE & ") "

DoCmd.RunSQL TheSQL

For i = 1 To 100

BN = BN +1
SS = SS + 2000
SE = SS + 1999

'This will take BN and add 1, so if BN was 2 it is now 3. 
'The next for loop, BN is now 3 and becomes 4
'Same for SS and SE, you add 2000 because it's 1999 + 1 record
'If SS = 1, it then becomes 2001 in the next loop, and 4001 in the next loop

TheSQL = "INSERT INTO tblBoxes ([Box Number],SerialStart,SerialEnd)" _
             & "VALUES (" & BN & ", " & SS & ", " & SE & ") "

DoCmd.SetWarnings False 'Important! It stops you accepting the insert 100 times!

DoCmd.RunSQL TheSQL

DoCmd.SetWarnings True

Next i

if it doesn't work (because it's untested) at least it's a start

 

[jamie23]

Great, this has worked really well.

I have now tried to add data from a combo box to update the table and i get the following message that i have attached as a word document.

You have been a great help!!!!!

 

[jdraw]

There is no Serial End number, is that the issue?

 

[jamie23]

The above code is on the Update after on the Logged by Field.

The code calculates the serial end number for us.

 

[jdraw]

Did you solve the 3075 error?
Can you post the SQL of the query involved?

 

[Chrisopia]

can you show us the code that you are using to retrieve the value of the combo box?

It sounds like you are referring to the value rather than the field...

 

[jamie23]

Hi Guys, Thank you all for showing an interest.

I have attached the code as a word Doc.

Many Thanks

 

[Chrisopia]

Ah

LB should be a string type, not combobox type. You've also called it LG instead of LB

Also i'm not sure if it would help, but try LB = Me.[comloggedby].value as well.

 

[jamie23]

Ah, Silly me!! Been staring at it for too long!

I have just made the changes as you suggested but i am still getting the same error. when i go to debug, it is highlighting in yellow the... DoCmd.RunSQL TheSQL

Any Ideas?

 

[jamie23]

Hi Guys,

Just about to leave for the day. I have attached a copy of the database that we are working on. Its in the early stages. I thought it may give you more of an idea of structure ect.

Thank you all!

 

[Chrisopia]

I can't seem to open it (perhaps it's because I'm still in 2007)... I would check the set up of the combo box. What is the bound column?
If it refers to an ID (i.e. the information that is stored from the combobox) then LB will need to be Long type not string.