INSERT INTO, isnt!

[NeoNemesis]

I have had a lot of help cobbling together a script which is designed to perform a solution to the following problem:

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.

The Code in the AfterUpdate() function

Dim i As Integer
Dim turn As Integer


Private Sub Max_Licence_Number_AfterUpdate()
For turn = 1 To nMaxLicenceNumber


Dim curdb As Database
Dim strVal As String
Dim SQLSTmt As String
strVal = GetLicenceInc(i)
Set curdb = CurrentDb()
SQLSTmt = "INSERT INTO [LicenceTable] ([Licence])VALUES('" & strVal & "')"
Debug.Print GetLicenceInc(i)
Debug.Print SQLSTmt
curdb.Execute (SQLSTmt)
Next turn

End Sub

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

A copy of the database can be found here:

http://www.cratefarm.vispa.com/digIT2help.zip

I just cant see why it is not working, mainly because i know next to nothing about VBA.

Regards

Joe

 

[KenReay]

Hi

Where does the variable i come from?

Where is trun defined (ie Dim Turn as Integer

Surely it should be:

Dim curdb As Database
Dim strVal As String
Dim SQLSTmt As String
Set curdb = CurrentDb()
For turn = 1 To nMaxLicenceNumber
strVal = GetLicenceInc(turn)
SQLSTmt = &quot;INSERT INTO [LicenceTable] ([Licence])VALUES('&quot; & strVal & &quot;')&quot;
Debug.Print strVal
Debug.Print SQLSTmt
curdb.Execute (SQLSTmt)
Next turn

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[NeoNemesis]

I have substituted the piece of code that you wrote into my script so it looks like this...

Dim turn As Integer


Private Sub Max_Licence_Number_AfterUpdate()



Dim curdb As Database
Dim strVal As String
Dim SQLSTmt As String
Set curdb = CurrentDb()
For turn = 1 To nMaxLicenceNumber
strVal = GetLicenceInc(turn)
SQLSTmt = &quot;INSERT INTO [LicenceTable] ([Licence])VALUES('&quot; & strVal & &quot;')&quot;
Debug.Print strVal
Debug.Print SQLSTmt
curdb.Execute (SQLSTmt)
Next turn

End Sub

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

However, it doesnt insert anything into the LicenceTable.

If you download the database (1.6mb) you can see for yourself.

 

[PaulBricker]

I modified your code a little. First, turn was starting at zero so your code was never running. Then i was never set to any value so even when the code ran,nVal was always the same '000'.
Currently this will put the value
001
002
003
etc.
into your LicenceTable.

Private Sub Max_Licence_Number_AfterUpdate()
'Dim i As Integer
Dim turn As Integer
Dim curdb As Database
Dim strVal As String
Dim SQLSTmt As String
turn = 1
For turn = 1 To Me.Max_Licence_Number



strVal = GetLicenceInc(turn)
Set curdb = CurrentDb()
SQLSTmt = &quot;INSERT INTO [LicenceTable] ([Licence])VALUES('&quot; & strVal & &quot;')&quot;
Debug.Print strVal
Debug.Print SQLSTmt
curdb.Execute (SQLSTmt)
Next turn

End Sub

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


Paul

 

[cmmrfrds]

It looks like you are in the process of designing your system. This is the time to make sure your data is normalized since it will make the system more flexible in the long term. Access is a tool that is designed to work well with normalized data and when you start to deviate from normalized data the data itself will cause unnecssary problems in manipulating the data.

Any field in a table should never have more than 1 meaning and no key field should have intelligence built into it.

MSE001001
MSE001002
MSE001003

These field are unique but also indicate sequence and this breaks normalization rules. Without getting into any details as it is a lengthy topic, I would encourage you to read up on how to normalize the data. It will lead to a more robust easier to maintain system.

 

[NeoNemesis]

PaulBricker: Thank you very much! At least i can see how the code inserts things into the table. It doesnt insert what i want it to as yet but I will try to develop it. If i need any more help then i know where to come!!

cmmrfrds: So by saying this do you mean that for each seperate Software title I have, there has to be a completely different table for them? Eg. [Licence_Word97] [Licence_Word2000] [Licence_WinXP]. And in each of them have the simple 001, 002, 003, entries in them?

 

[cmmrfrds]

No, the software table looks like it is probably normalized and all the fields are dependent on the key field. Things like Name, VEndor, and Serial are dependent on the key which could be license number.

The license info table appears to need more definition.
For example I could see the following tables.

SoftwareTitle which probably can contain license info.
STID primary key

Computer
CID primary key

ComputerLicenses
CLID primary key
STID foreign key
CID foreign key
sequence?

In effect the license is related (relational) to the pc through the ComputerLicenses table whose logical key is the combination of the foreign keys. If you need sequencing add another column to the table and populate records until it reaches the max, then error out the next insert.

 

[NeoNemesis]

Goodness me, this is going to be far more mammoth than i first thought. You seem like you know what you are doing and i am a complete amature that has unfortunately been thrown in at the deep end. I hope you dont mind if i pick your brains further?