strategy

[rnpIII]

G' Mornin' Ladies and Gentlemen,

I have a little problem and need some direction.
I have a button on a form which I would like to click and have three (or however many I want) different records added to the table in question.

I am confounded by the "for" loop, always have been. The logic doesn't quite click with me.

Since I am adding "different" records I don't know how I would use the loop.
Say the table is:
Date
Identifier (Integer)
Assetnum
SoftID (Assetnum & Identifier)

I can get one record to update using the "recordset.addnew" setup but can't figure out how to do the rest.

Any suggestions or assistance will be greatly appreciated.

rnpIII

 

[StaceyG]

How is the user inputing the information that makes this button need to input different numbers of records?
What you could do is create an AddRecord procedure with an argument of whatever the differences are for each record. For instance, let's say your AssetNum is the thing that varies for each new record, and the user is able to add these AssetNum to a listbox and then click Add Record.
You could do something like this:

Private Sub cmdAdd_Click()
dim i as integer
For i= 0 to lstAssetNum.ListCount-1
'we subtract 1 from the number in the list because the last
'item (say #10) has a listindex of 9
Call AddRecord(lstAssetNum(i).Text)
Next i
End Sub

Private Sub AddRecord(strAssetNum as String)
With RS
.AddNew
.Date=Format(now(),"mm/dd/yy&quot
.AssetNum=strAssetNum
.Update
End With

Hope that is not too confusing and kind of answers your questions!
Stacey

 

[sunaj]

Or if the records allways goes together:

Rs.AddNew Array("Date","Identifier","AssetNum","SoftID&quot, Array(Format(now(),"mm/dd/yy&quot,MyID,MyAssetNum,MySoftID)

Where the variables MyID, MyAssetNum and MySoftID contains the values to be inserted.


Sunaj

 

[rnpIII]

Hi Stacy,

Thanks for the code. Don't think that will quite work here, or I am just being obtuse. I suppose a little background might help better.

The assetnumber is constant; it is written to a variable "recdel"
As the button is clicked it will add the same three pieces of info each time, but only once per assetnum ( have the error code to catch this).

The added info is also constant, it will be the same set of info each time it may be added.
For instance:
Date = Now()
Assetnum = recdel
Identifier= 1 or 3 or 23 These cannot be changed by the user.
SoftID = recdel&identifier

 

[rnpIII]

Hello Sunaj,

I have created an object with the info. Just can't figure out how to get three seperate records into the table.
The above code is good for one record from what I can gather.

rnpIII

 

[sunaj]

Hi,

If we look at the table as row and columns:
My method adds a new row with data in all columns. This method is prefreeable if you always insert something in all the columns (its briefer and it makes sure that you don't forget something). If some of the rows don't have data in all columns then you should use Staceys method.

Loop through your object that holds the data and use one of the 2 methods to insert rows in the table.

Sunaj

 

[rnpIII]

So,

Woud I enter your code in the builder three times?
I am have a real problem understanding the logic behind a loop.
I see the usefulness in them it just doesn't click.

rnpIII

 

[StaceyG]

So are you saying that you always need to add 3 records, one with the identifier 1, the other 3, and the other 23 per each assetnum? Forgive me if I'm being dense, Just trying to sort out the best way to help

 

[sunaj]

As I understand your database:
Your table contains 4 columns :
-Date
-Identifier (Integer)
-Assetnum
-SoftID (Assetnum & Identifier)

To insert a new row with these fields you only need to do this once(no loop):
Rs.AddNew Array("Date","Identifier","AssetNum","SoftID&quot, Array(Format(now(),"mm/dd/yy&quot,MyID,MyAssetNum,MySoftID)


Sunaj

 

[rnpIII]

Hello Sunaj and Stacy,

Stacy, you are correct, I need to insert (add) three new rows.
One with the Ident of 1
One with the Ident of 3
One with the Ident of 23 Or whatever other numbers will be substituted.

So the Assetnum, date and Ident will be constant, FOR THAT PARTICULAR RECORD.
When I go to a new record in the parent, I will have a different Assetnum, but still have the same date, same Ident's the SoftID will change because the Assetnum has changed in that case, hence the reason for the variable "recdel".

Once again, Sunaj, and I am not the quickest person on code, so please forgive me, but I do not see how the supplied code will add three different records with three different SoftID's.
I am not really quick but I do not expect you to hold my hand, although you really are and I do appreciate it.

rnpIII

 

[sunaj]

Hi,

I misunderstood you. to insert 3 row you'll have to do 3 inserts:
Rs.AddNew Array("Date","Identifier","AssetNum","SoftID&quot, Array(Format(now(),"mm/dd/yy&quot,1,MyAssetNum,MySoftID)
Rs.AddNew Array("Date","Identifier","AssetNum","SoftID&quot, Array(Format(now(),"mm/dd/yy&quot,3,MyAssetNum,MySoftID)
Rs.AddNew Array("Date","Identifier","AssetNum","SoftID&quot, Array(Format(now(),"mm/dd/yy&quot,23,MyAssetNum,MySoftID)

This is probabply NOT the way to do it, but I don't know enough about your program to determine the best way.
Do you allways have to insert 3 rows each time the user clicks a button?
How it the Indentifier, AssetNum and SoftID given?

-If the Identifier is just an identitifier you should make it an autonumberfield so you don't have to worry about inserting values in that field.

Sunaj

 

[StaceyG]

rnpIII,
How do you know what your identifier numbers will be? You said they'd be 1, 3, and 23 or whatever other numbers. Do these numbers come from a textbox, listbox???
If they did come from a listbox type control you could just change around my first code above and pass the identifier as the argument. Otherwise, those identifiers could be put into a variable array which could then be incorporated into a loop for adding all new necessary records.
Stacey

 

[rnpIII]

Hello Stacy and Sunaj,

Please find below the "current" piece of code for this button. I have tried many variations with the same result, either the first or the last record will be entered, but it will alway miss the other two.

Sunaj, your code example looks eerily familiar to something that I tried earlier, which yielded one record being added, but not all three.

I think this code is seriously cluttered but as I said before, I am not real good with this stuff. The table structure has already been revamper per MichaelRed's suggestions. He made a good point so I did just that. Now, of course, it just making the correct tweeks to the code for the new structure.

Think I could just make another table with these three records and then do an append replacing the assetnum and softid?

Stacy, the IDNum's are based on an autonumber from another table which is the linking info to this table. The Assetnum is a secondary link and plays an integral part in the tracking of the software (SoftID) which is the Asset number concatenated with the ID number.

I use the ID number instead of the name of the product for ease of tracking.

Private Sub NewInstBtn_Click()

Dim MyCol As New Collection, MyDb As Database
Dim MyRst As Recordset, SQLStr As String, I As Integer
Dim UpDateStr As String, UpdateRst As Recordset

SQLStr = "SELECT [License User].AssetNum," & _
" [License User].IDNum, [License User].SoftUserID," & _

rnpIII
"[License User].DateIssued, LicensesQry.IDNum," & _
"LicensesQry.ProdName, LicensesQry.Available," & _
"LicensesQry.[Cost of one license]" & _
"FROM [License User] LEFT JOIN LicensesQry" & _
"ON [License User].IDNum = LicensesQry.IDNum"


UpDateStr = "Select [License User].AssetNum," & _
"[License User].IDNum," & _
"[License User].SoftUserID, " & _
"[License User].DateIssued" & _
" FROM [License User]"

On Error GoTo Err_NewInstBtn_Click

Set MyDb = CurrentDb
Set MyRst = MyDb.OpenRecordset(SQLStr)

MyCol.Add 44, "NT4"
MyCol.Add [Recdel] & MyCol("NT4&quot, "NT4ID"

MyCol.Add 29, "Off2K"
MyCol.Add [Recdel] & MyCol("Off2K&quot, "Off2KID"

MyCol.Add 43, "Inoculan"
MyCol.Add [Recdel] & MyCol("Inoculan&quot, "InocID"

MyCol.Add nn, "x" 'reserved for new additions later
MyCol.Add [Recdel] & MyCol("x&quot, "xID"

If Recdel = "" Then
MsgBox "You cannot perform a new install" & vbCr & _
"after having completed one!" & vbCr & _
"According to my transactions information," & vbCr & _
"you have just clicked this button.", , "Error"

Else

UpdateRst.AddNew
UpdateRst!AssetNum = Recdel
UpdateRst!DateIssued = Now()
UpdateRst!IDNum = MyCol("NT4&quot
UpdateRst!SoftUserID = MyCol("nt4id&quot
UpdateRst.Update
Next

End If

Exit_NewInstBtn_Click:
Exit Sub

Err_NewInstBtn_Click:
MsgBox Err.Description
Resume Exit_NewInstBtn_Click

End Sub