How to calculate licenses left using query?

[Jerx]

Hello all,

Iam a newby to Access but for learning purposes iam building an Access Database in Access 2007 (a CMDB). In the database i got so called configurations. In a configurations there are persons, computers, model, OS, software etc. linked.

For the Software part I want a Query that calculates how many licences I got left. But I really cant figure out how I can do that... So iam hoping if someone can help me on track.

Some DB info:
Iam keeping it smaller then my whole DB looks like.
TblConfig:
c_ComputerName Text PK
c_ParentOU Text
c_PasswordAge Number
c_Model Text
c_Tagnumber Text
c_Memory Text
c_Bios Text
c_Users Text
c_Operating system Text
c_Software Text
c_Opmerking Text
c_Garantue Number
c_Location Number

tblSoftware:
s_SoftID AutoNumber
s_Software Text (PK)
s_Licenses Number
I used not the autonumber as PK due it gave me some trouble. I will explain below.

Under the s_Software i got my Software like Office 2007 etc. And on s_Licenses the amount of licences a software got.

In the form configuration you can select the software a configuration/pc got. This selection is saved in c_Software. But this field is a Multiple Value field (I know its probably evil but I wanted that user could select multiple selections).

So I was figuring that if you select software you can AfterUpdate a Query event that will change how many Licences I got left. Hope I was clear enough..

Thanks in advance :)

Jerx

[ADezii]

This is actually a 2-Step Process:

1. When a Software is selected, let's say from a Combo Box named cboSoftware, you must make sure that you have at least 1 License remaining for that Software. If there are none left, Cancel the Update of the Combo Box. This could be done in the BeforeUpdate() Event of the Combo Box:
   

   Private Sub cboSoftware_BeforeUpdate(Cancel As Integer)
   Dim intNumOfLicenses As Integer
    
   If IsNull(Me![cboSoftware]) Then Exit Sub
    
   intNumOfLicenses = DLookup("[s_Licenses Number]", "tblSoftware", "[s_Software Text] = '" & Me![cboSoftware] & "'")
    
   If intNumOfLicenses = 0 Then
     MsgBox "You have no Licenses remaining for " & Me![cboSoftware], vbExclamation, "No License Available"
       Cancel = True       'Prevent Update of cboSoftware
   End If
   End Sub

2. At some point, Update the Software Table and decrease the Number of Licenses for the chosen Software by 1:
   

   Dim intNumOfLicenses As Integer
    
   If IsNull(Me![cboSoftware]) Then Exit Sub
    
   intNumOfLicenses = DLookup("[s_Licenses Number]", "tblSoftware", "[s_Software Text] = '" & Me![cboSoftware] & "'")
    
   If intNumOfLicenses = 0 Then
     MsgBox "You have no Licenses remaining for " & Me![cboSoftware], vbExclamation, "No License Available"
       Cancel = True       'Prevent Update of cboSoftware
   End If

P.S. - I accidentially posted duplicate code, I'll Post the other Code Updating the License Number Count when I get to work tomorrow evening, since that is where the Code is.(LOL).

[Jerx]

Cool, iam gonna try this tomorrow and report in later then :)

Edit: Ahh i see you just posted duplicate :P I will wait for it.

[ADezii]

Dim strSQL As String
 
strSQL = "UPDATE tblSoftware SET tblSoftware.[s_Licenses Number] = (tblSoftware.[s_Licenses Number] - 1) " & _
         "WHERE tblSoftware.[s_Software Text] = '" & Me![cboSoftware] & "';"
 
CurrentDb.Execute strSQL, dbFailOnError

[Jerx]

Thanks gonna try it right away (and yes iam spamming refresh this website and my mailbox every 5seconds :P

[Jerx]

Ok ive been trying a bit but it seems to go wrong. Say that i got 2 forms, 1 is a configuration form where you can select Software, model, tagnumbers etc. And i got a form Software where they can Add software and licences.

In the form configuration is a list. So I figured that I implement the first part of the code in the before update and edit the cboSoftware to lstSoftware.

1 thing it crashes on is:

intNumOfLicenses = DLookup("[s_Aantal]", "tblSoftware", "[s_Software] = '" & Me![LSTSoftware] & "'")

So I think its looking for S_Aantal or S_Software form/table in the form configuration?

[ADezii]

What does the [s_Aantal] Field have to do with the Number of Licenses for a given Software Package?:

[Jerx]

Sorry S_Aantal = S_License (i changed due its a dutch word :P My mistake ;o ) But the question still stands.. thanks for putting your time in it though.