VBA Newbie, please help! (2 Viewers)

[NeoNemesis]

I have got a little bit of a problem, one that i would like some help getting my head around, it involves MS Access 2000 and its VBA....

I will attempt to explain it properly.

I am making a database system the will catalogue all of the software that a company uses. There are many software titles, such as MS Word, MS Excel or WinXP. As this company wants to be completely legal they attempt to only have each program installed on as many machines as they have licences for them, this can be a problem if no-one knows what is where, hense this system.

Using the three examples above...

I have given each software package a unique identifier, for MS Excel it is MSE001 (MS Excel 2000 1), MS Word is MSW001 and for WinXP is MSWXP.

Now i have another table called "Licence Info". I want this to be populated automatically as I enter the details of any software titles into the database. The details will go into the "Software" table. As i enter the details of each software package I will put in all of the standard things (Name, VEndor, Serial etc) and with these details will also go the "Max licence number", for example there are 10 licences bought for Excel so the number in this field is 10.

Now here comes the bit that i need help with. I would like to have the Licence entity populated with each software title multiplied by its Max Licence value. Now onto the result i would like to have 001, 002, 003 etc put on the end, so it would result as:

MSE001001
MSE001002
MSE001003
MSE001004
etc
MSW001001
MSW001002
etc

These numbers become the unique ID of each Licence. Each one of these can then be assigned to the computer which it will be installed on:

MSE001001 PC001
MSE001002 PC002
MSE001003 PC003
MSE001004 PC004
etc
MSW001001 PC002
MSW001002 PC010
etc

What i would like help with is scripting the code that will populate my "Licence" table with the Licence ID's only. I have very little experience of VB but i know BASIC fine.

Any help will be greatly appreciated. If you need any other details just gimme a call!!

Thank you in advance,

Neo

 

[GJT]

In the After_Update() function for the form try something similar to this :

Dim strVal as String

For i= 1 To nMaxLicense
' create license item
strVal = GetLicenseInc(i)

CurrentDb.Execute("INSERT INTO LicenceTable (License)VALUES(" & strVal & ")"

Next i

Private Function GetLicenseInc( nVal as integer) as String
' add string prefix
If nVal <10 Then
GetLicenseInc = "00" & Val(nVal)
Elseif nVal >=10 AND nVal <100 Then
GetLicenseInc = "0" & Val(nVal)
Else
GetLicenseInc = Val(nVal)
Endif
End Function


When you update the master record the loop willl iterate through 'n' instances of the licence and create the subset of records for that software title.

I trsut I have interpreted your reqs. correctly.

 

[NeoNemesis]

Thanks for you're help. THis may sound pathetic but i have never used VB in Access before, is there any chance that you could label each significant step in the procedure, so that i can then look up the separate subjects.

Oh, just as a post script. My Licence entity is called "LicenceTable", it has one field in it, "Licence" which is the primary key.

I have modified the code to this:

Private Sub Max_Licence_Number_AfterUpdate()
For i = 1 To nMaxLicenceNumber
' create license item
strVal = GetLicenceInc(i)

CurrentDb.Execute("INSERT INTO LicenceTable (Licence)VALUES(" & strVal & ")"

Next i

Private Function GetLicenceInc(nVal As Integer) As String
' add string prefix
If nVal < 10 Then
GetLicenceInc = "00" & Val(nVal)
ElseIf nVal >= 10 And nVal < 100 Then
GetLicenceInc = "0" & Val(nVal)
Else
GetLicenceInc = Val(nVal)
End If
End Function

have i ruined anything?


Kindest Regards

Joe

 

[GJT]

Joe

Not too sure what you require from me. Code looks fine.

Basically, you are :

1. In the AfterUpdate event i.e. when Access has updated the bound fields on the form, you are
1.iterating through the loop upto 'n' licenses
2. getting the incremented license number via the GetLicenseInc function
3. The if statement cconstructed to allow for all numeric scenrios
to generate the identifier as requested

does the code work???????

 

[NeoNemesis]

does the code work???????

Unfortnately i cant get it to work as yet, i will keep playing!

I get to the debugger screen and the first line is higlighted in yellow. And the "i" in the " strVal = GetLicenceInc(i) " line is highlighted.... i am assuming that this means that there is a problem somewhere there?


Joe

 

[GJT]

Have you declared var i?

eg. Dim i as Integer

You may also need to delimit strVal with quotes as it is a string.......

eg.
VALUES(' " & strVal & " ')"


OK- let me know........

 

[NeoNemesis]

Hi there, thank you for your help so far it is really appreciated...

I have made the neccesary changes and now when i change the value in the "MaxLicenceNumber" Field there is no error message...RESULT!

However, there is nothing inserted into the "LicenceTable" entity.

THe code is now as follows (under the AfterUpdate() function):

Dim strVal As String
Dim i As Integer

Private Sub Max_Licence_Number_AfterUpdate()
For i = 1 To nMaxLicenceNumber
' create license item
strVal = GetLicenceInc(i)

CurrentDb.Execute "INSERT INTO LicenceTable (License)VALUES('" & strVal & "')"

Next i

End Sub

Private Function GetLicenceInc(nVal As Integer) As String
' add string prefix
If nVal < 10 Then
GetLicenceInc = "00" & Val(nVal)
ElseIf nVal >= 10 And nVal < 100 Then
GetLicenceInc = "0" & Val(nVal)
Else
GetLicenceInc = Val(nVal)
End If
End Function

 

[GJT]

The code looks fine but you will of course need some sort of provision in your routines to ensure that if the user increments/decrements the license number the appropriate recordset sets are either amended / deleted.

Is the license field bound to the appropriate table field?

 

[NeoNemesis]

The code looks fine but you will of course need some sort of provision in your routines to ensure that if the user increments/decrements the license number the appropriate recordset sets are either amended / deleted.

Is the license field bound to the appropriate table field?

Im sorry, i feel so stupid, I am trying research on the side in an attempt to get this to work but i just cant.

It appears that everything is ok, but evidently it is not.

What do you mean by the provision in your routines etc.?

Neo

 

[GJT]

If the user decides to change the number of licenses for some reason - you will need to add/delete the appropriate number of licenses from the source table.

Otherwise - as soon as the update routine gets called again - it will add a further 'n' licenses to your database all over again!