SOLVED: MS Access multiples question

[ptvGuy]

I'm creating an MS Access database that allows users to enter item information along with an item number for donated items. However, if there are multiples of a specific item--such as twelve concert tickets--I don't want them to have to enter all that information twelve seperate times. I want them to just enter the number of multiples on the form and have the database create the twelve seperate records based on the current record. However--and here's the catch--the item numbers of these seperate records will each have to have the original item number with a unique letter appended to the end.

For instance, the twelve concert tickets might be entered initially with the itemNumber set as C126. The user would then enter 12 in the itemMultiples field. The database would then automatically create the twelve records with the item numbers C126a, C126b, C126c, C126d, C126e, C126f, C126g, C126h, C126i, C126j, C126k, and C126l and would delete the initially created record with the C126 itemNumber.

I realize that there's no easy way to do this, but I would certainly appreciate any advice anyone may have on how to approach this. It seemed like such a basic, simple thing to do when I agreed to make that a part of the database, but it's turned into a major snag for me.

[Cosmos75]

Not my best work (did it quickly in 10 mins or so) but it should give you an idea as to what to do.

Must go sleep now! Did not sleep last night!
[vba]Private Sub myButton_Click()
Dim myDB As DAO.Database
Dim myRst As DAO.Recordset
Dim CharAlpha As Integer, ZEndNum As Integer, itemNum As Integer
Dim Zend As String, EndAlpha As String

Set myDB = CurrentDb()
'Set table to add records to
Set myRst = myDB.OpenRecordset("Table1")

CharAlpha = 97
Zend = ""
ZEndNum = 97
itemNum = Me.itemNumber

For i = 1 To Me.txtTickets
'This sets the string to be added to the end of ItemNumber
'Char(97) = a --> Char(122)= z (LOWER CASE)
EndAlpha = Zend & Chr(CharAlpha)
With myRst
'Add new record
.AddNew
'Set value of Field2
.Fields("Field2") = itemNum & EndAlpha
'update record. Must be done to save record
.Update
End With

'increase by 1 for next alphabet
CharAlpha = CharAlpha + 1

'This will allow for a two-character ending.
'e.g after z comes aa, ab and so on...
'so you can have up to (26*26) + 26 records
If CharAlpha > 122 Then
Zend = Chr(ZEndNum)
ZEndNum = ZEndNum + 1
CharAlpha = 97
End If
Next i

'Close object to avoid database bloat
myRst.Close
myDB.Close
End Sub[/vba]If I wasn't tired I'd add the capability to handle as many ending alphabets as need, e.g. C126zzzzzza but I hope 702 unique records (a --> zz) is enough.

Hope this helps!

[ptvGuy]

Okay, now I feel like a complete idiot. I was approaching the whole thing "bass-ackwards". It makes perfect sense now. Thanks.

[Cosmos75]

ptvGuy,

Glad that helped!


Do you need to be able to add more than 702 tickets (or other donated items)? If so, I'd be glad to add that to the code when I have time and then post back here. Or maybe someone else here will beat me to it!

Good luck!

[ptvGuy]

Do you need to be able to add more than 702 tickets (or other donated items)? If so, I'd be glad to add that to the code when I have time and then post back here. Or maybe someone else here will beat me to it!

I can't imagine that I ever would, but it would still be a useful bit of code to add to our archive here. Maybe Dreamboat could include it in her newsletter as a useful code snippet. Thanks, though.

[Cosmos75]

I can't imagine that I ever would, but it would still be a useful bit of code to add to our archive here. Maybe Dreamboat could include it in her newsletter as a useful code snippet. Thanks, though.

OK, glad that will work for you!

I will try and work on getting some code to allow for any number of items. Will post it here or another thread (if this one is closed as it is solved).

Not wanting to be presumptuous - your question is solved, right?