INSERTing and Removing records

[NeoNemesis]

Hi, I am a complete novice when it comes to VB coding. I have managed to cobble together the following script so which adds records into a table called "LicenceTable". The problem.

I am creating a database that will (hopefully) be able to catalogue the Software Titles that my company has and will be able to record which software is installed where. This means that if there are 10 copies of MS Word 2000 (the Id of this is MSW001, the value in the "MaxLicenceNumber" for this field will be 10 and 10 records will be inserted into the "LicenceTable" so it will look like this

MSW001001
MSW001002
MSW001003
MSW001004
etc.

The script below adds records into this table in the format:

001
002
003

The script loops until the "MaxLicenceNumber" is reached. However in front of this number i would like to insert the string contained within the "ProductID" field of the same table that "MaxLicenceNumber" is on.

If the user decreases the value in the "MaxLicenceNumber" field then it will launch a form which will allow him to remove licences.

Here is the current code...

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
Dim PackageID As String

turn = 1
For turn = 1 To Me.Max_Licence_Number
strVal = GetLicenceInc(turn)
Set curdb = CurrentDb()
SQLSTmt = "INSERT INTO [LicenceTable] ([Licence])VALUES('" & strVal & "')"
Debug.Print PackageID
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


Can anyone help??

Regards

Joe

 

[JeremyNYC]

Joe,

You'll thank yourself a LOT if you don't concatenate these values like this. Separate values should be stored in separate fields. You can always concatenate for display on forms and reports. But it becomes a big headache to pull things apart for sorting and searching and filtering if you do it this way.

Check out the Fundamentals article on the Developers' section of my web site for more on this.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[NeoNemesis]

I have to have these details collected together because they are creating a unique identifier for each Licence. I just decided that the ID should not just be a random code.

 

[JeremyNYC]

Your primary key, or any other index can be made up of several fields. I would suggest you use the autonumber and make an index on all of these fields. You don't have to ever show the autonumber to your users.

In any case, it's really not a good idea to have compound fields like that. The article I mentioned will tell you why.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[NeoNemesis]

Ok i think i know what you are getting at.

In the LicenceTable i have these fields:

ProductID
Autonumber and make them composite keys.

so i will basically have my MSW001001 just spread out into two fields?

 

[JeremyNYC]

It looks to me like three fields:MSW 001 001. If there will ever be MSW002001, you'll want to break that middle part out, too.

Definitely read that article on my site. It's great.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[NeoNemesis]

The breakdown of the Key is

MSW [Microsoft Word]
00 [2000]
1 [I dont actually know why i put the one on...maybe that should go]
001 [the licence identifier]

Ok so the breakdown is now

MSW [Microsoft Word] 00 [2000] 001 [Licence Identifier]

The extra &quot;1&quot; did break up the zeros well though.

I couldnt find that article on your website, could you give me a direct link??

Joe

 

[JeremyNYC]

Yeah, that looks like the perfect way to break up that field.

I don't know the direct links. Go to the Developers' section and then click on the link to Fundamentals of Relational Database Design.

Jeremy
=============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.

 

[NeoNemesis]

Thanks i found it in the end. I appreciate your help greatly. I understand why i have to break that code up. Do i have to break it up wherever it appears in the database? I am assuming so.

Can you lend me any help with the coding, i hope that you can code as well as you can teach database theory!!

Would it help if i zipped up a copy of my project and uploaded it to my webspace?

 

[JeremyNYC]

Yes, you'll want to break it up everywhere it appears in you database. But the great thing about using an autonumber is that it replaces those three fields in all your other tables, so you won't actually have those three fields appear in too many places.

I would suggest you read the article and then re-think your table structure.

As far as other help, that's what the forum is here for. I answer lots of questions here for free. But I am a professional developer, and I charge for my time outside of these free forums.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.