AfterUpdate Event

[scouser]

I would like to put a check on a field on a form.

The user selects a computer they wish to allocate software to. They then select the Software and finally the license associated to the software.

After they select the license I would like access to query a query!

qryALLOCATED_LICENSE_AVAILABILITY has a field that keeps a running total of the number of licenses that are available for allocation.

I tried

Private Sub cboLicenseID_AfterUpdate()
If (qryALLOCATED_LICENSE_AVAILABILITY.Available <= 0) Then
MsgBox "You must purchase additional licences"
End Sub

I fully expected an error and was not dissapointed as my coding is terrible.
How can I complete the code: End IF...............Else.....
Thanks,
Phil.

 

[Brianwarnock]

Do you need the Else, what are you going to do? If nothing then don't use the block form
Private Sub cboLicenseID_AfterUpdate()
If (qryALLOCATED_LICENSE_AVAILABILITY.Available <= 0) Then MsgBox "You must purchase additional licences"
End Sub

or perhaps if you prefer the block form you can presumably code

Private Sub cboLicenseID_AfterUpdate()
If (qryALLOCATED_LICENSE_AVAILABILITY.Available <= 0) Then
MsgBox "You must purchase additional licences"
Else:
EndIf
End Sub


Don't worry about it tho' tonights game is more important

Brian

edit actually in the block form I think you can ommit the ELSE and just have trhe EndIf

 

[Dennisk]

2 - 1 to Liverpool

 

[scouser]

Game

Pessimist.....can't see Chelsea NOT scoring. Can we get 3.......only if Robbie plays!!

OK. The code returns as error: Object Required:

Private Sub cboLicenseID_AfterUpdate()
If (qryALLOCATED_LICENSE_AVAILABILITY.Available <= 0) Then
MsgBox "You must purchase additional licences"
End If
End Sub

I have a sample DB posted here:
http://www.access-programmers.co.uk/forums/showthread.php?t=127471&page=2

Thanks,
Phil.

 

[Brianwarnock]

The solution is more complex than just tweaking the If which should be like

Private Sub cboLicenseID_AfterUpdate()
If qryALLOCATED_LICENSE_AVAILABILITY.Available <= 0 Then
MsgBox "You must purchase additional licences"
End Sub

but qryALLOCATED_LICENSE_AVAILABILITY.Available is not available to the code, I don't think I'm going to come up with a solution in any reasonable time frame. I hope somebody else can


Brian

 

[scouser]

Trying

2-1 and we are out so lets try 2-0
Thanks for trying Brian.
Phil.

 

[Rich]

How are you getting the value from the query, you either have to open it or use a DLookup or DCount to get the value?

Oh and don't worry about tonights result, it's a foregone conclusion that MU will take the triple

 

[scouser]

M......

Can't even bring myself to utter the words.............
Joking aside you have to admire United & Chelsea. Liverpool have spent a fair bit over the past 6 years......just not well..........

Not sure on the query as my ideas are great but execution CRAP!!

DLOOKUP would sound like a winner, how would that look?
Phil.

 

[gemma-the-husky]

can you get at a field within a query by the syntax queryname.fieldname?

i thought you would have to use

variablename = dlookup("fieldname","queryname","where clause if necessary)

and then handle the value you have retrieved.


so


Private Sub cboLicenseID_AfterUpdate()
dim avail as long

avail = dlookup("available","qryALLOCATED_LICENSE_AVAILABILITY")
if avail <= 0 Then
MsgBox "You must purchase additional licences"
end if
End Sub

 

[scouser]

Good Effort

Gemma good effort. No errors. However the message does not appear? Just allocated down to -1.
Any thoughts?
Phil.

 

[scouser]

It works

I eat my words. It does work (kind of)?

If I allocate license 1 down to zero but license 2 still has 5 available then the message does not appear. If license 2 reads 0 then I allocate the last license 1 then the message pops up?

I think one of the problems might be that after you select the license key to allocate it does not update the query until you have clicked out of the current record being entered....although this does not explain why I can continue to allocate licenses with negative values!!
Phil.

 

[Brianwarnock]

Could you do the test in the event ie If (avail-me.NumberOfLicenceAllocated) >= etc, obviously that field would have to be entered first or maybe change the event to erm ?

Just floating ideas

Brian

also puzzled that you don't need criteria, eg licenseId/key in the Dlookup

 

[scouser]

Head Spinning

Brian my head is spinning. I have spent 2 days solid on this DB trying to improve the licensing side of things!! Every time I thought I had it I found a flaw. I had that many versions I didn't know what query was doing what!!

Nearly there.....no more improvements!!
Phil.

 

[scouser]

License ID

Had a thought. Maybe the code is being driven by the software ID.

So if:
SoftwareID 40 has 2 licenses associated to it:

License ID 1 = 50 user
License ID 2 = 5 User

So if I run the licenses down for license ID 2 but there are still licenses available for software ID 40 then the message will not be generated. I guess I need the code to also reference the LicenseId within the same query?

Any ideas, my recent efforts have all failed!
Thanks,
Phil.

 

[Brianwarnock]

A sort of solution put the following in the form after update event

Private Sub Form_AfterUpdate()
Dim avail As Long
avail = DLookup("available", "qryALLOCATED_LICENSE_AVAILABILITY", "LicenseID =" & Me.LicenseId)
If avail <= 0 Then
MsgBox "You must purchase additional licences"
End If
End Sub

It works ok but you must click on the next rec to activate it, there must be away to force things but my time out of the sun is up
I put this in the form event so that the user can change the number of licences without having to reselect.
Brian

 

[scouser]

Thanks

Thanks Brian.
Lets hope tonight goes as well!!
Phil.

 

[Brianwarnock]

I hope the reds can remember their skills a bit better than I do.
I'll be watching witha rabid Evertonian, Chelsea's actions last time made him switch support to the reds, an unheard of event.

Brian

 

[scouser]

Get In

GET IN................is this becoming a football post?

What game was Mourwhingio watching? Best team won I feel on the night.
Feel free to argue.

 

[Brianwarnock]

Only Jose would argue, we were robbed of a perfectly good goal by Kuyt, the linesman must have worse eyesight than me.

My son in law, the rabid Evertonian , was so annoyed at Chelsea's diving that he again wanted Liverpool to win, that's twice now in his lifetime, , he still doesn't want us to win the cup , hoping for Milan.

I hope you DB is working ok now, sorry I couldn't come up with a really smart answer.

Brian

 

[scouser]

Db

Brian the answer you gave is sufficient for my needs.
I was in a pub surrounded by Chelsea supporters......amazing how brave you get after 5 pints!!

I am also hoping for Milan as our record against MU is not so good.....plus I would NEVER get over losing to them!!
Many Thanks,
Phil.