Set up Inventory Control

  • Thread starter Thread starter Needsomehelp
  • Start date Start date
N

Needsomehelp

I'm trying to set up a way to make sure a class isn't oversold. I have a
table with the class size, and a query [space available] that sums up the
spots sold in the class based on the table [session info]. If someone sells a
spot that will oversell that session, I want a msgbox that says do not sell
this. Where do I start?
Thanks!
 
i presume you are doing this through a form if not then you need to if
so then all you need to do is when they click ok to make the sale you
need to compare that value against the value in space avaliable where
[session info].[sessionid] = [space avaliable].[sessionid] if the
[space available].[value] - [value staff member has entered] is a
negitive number ot < 0 then you can have a line of code msgbox "there
are not enough seats avaliable"

it would look something like

if [space available].[value] - [value staff member has entered] < 0
then
msgbox "there are not enough seats avaliable " & [space
available].[value]
else
{do the update table code here}
end if

for more info you can ask on the forms group.

Regards
Kelvan
 
Thanks for the quick post.

I've got it set up on a form, and I used a "after update" event procedure.
I've tried a macro, and I can open the query[spaceavail], filter it to the
right [class id], but when I put in the statement:

iif([avail]>1, msgbox("good job"), msgbox ("don't oversell this class")

I get the "object doesn't contain the automation "avail" message.

I don't know enough about VB to write the code, and the book I bought is
really lousy and doesn't explain it.
 
ok iif is the if satatement for queries

if is for VBA

try

if recordset.[avail]>0 then
msgbox("good job")
{do the update query commands here}
else
msgbox ("don't oversell this class " & recordset.[avail] & " spots
are avaliable")
end if

....... actually i just realised what you are doing you are using bound
objects rather than recordsets.

i am not that familer with bound objects as i use recordsets all the
time they are more reliable and eaiser to control.

how are you storing the data for the classes are you having a max
value in one table and in the other table where poeple come in and
trying to compare thoes two values or are you subtracting a number
form that value till it reaches 0

if you want to control it you are going to have to use a record set

try this code in the after update
(note this isnt tested)

dim db as database
dim rst as recordset

set db = currentdb()
Set rst = Db.OpenRecordset("space available", dbOpenDynaset)

if rst.[avail]>0 then
msgbox("good job")
DoCmd.DoMenuItem acFormBar, acEditMenu, acSaveRecord
else
msgbox ("don't oversell this class " & rst.[avail] & " spots are
avaliable")
DoCmd.DoMenuItem acFormBar, acEditMenu, acundo
end if

hope this helps

Regards
Kelvan
 
Sorry to be such a pain...

How do I reference the query so vba knows I'm looking for [avail] in
[queryspaceavail]? I get an error "method or data member not found", and teh
RST.[avail] line is highlighted. My form is not based on this query.

My form is based on an individual child's record, and the information is
going into [sessioninfo], a subform that records the classes paid for. The
queryspaceavail sums up the classes purchased, and in that query, I have a
calculated field [avail] that is made up of the field [ttl inventory] - [sum
of classes purchased].


Thanks!
 
in your form at current can you make the [avail] information display
or is it no where to be found.

let me make a note i am more proficant with queries not forms if you
want more prompt help you coudl try the .forms group
 
You can use the DLookup function to examine the value of the avail column in
any particular row returned by queryspaceavail. You will need to identify
the row by means of the class ID column's value.

From your description I assume each row in the subform represents a single
space being purchased at a session. To prevent a row being inserted for a
fully booked session (which I take it would be indicated by a value of zero
in the avail column for the session in question in the queryspaceavail
query's result set) put code in the subform's BeforeUpdate event procedure.
This has a Cancel argument whose return value can be set to True to prevent
the row being inserted into the table. So the code would be like this:

Const conMESSAGE = "There are no places available for this session."
Dim strCriteria As String

strCriteria = "[class ID] = " & me.[class ID]

If DLookup("avail", "queryspaceavail", strCriteria) <= 0 Then
MsgBox conMESSAGE, vbInformation, "Warning"
Cancel = True
Me.Undo
End If

Should the Class ID column happen to be of text data type amend the code as
follows:

strCriteria = "[Class ID] = """ & me.[class ID] & """"

You'll notice that I've tested for <= 0 rather than for = 0. This is just
in case its possible for a session to be deliberately oversold. You wouldn’t
be able to oversell a session via the subform of course as once you hit zero
the above code makes it impossible to save the row, but you might have some
other means of dong so. Even if its never possible to oversell a session
legitimately the code will still work.

Apologies in advance if you respond to this and I don't get back to you, but
after tomorrow I'll be away incommunicado for a while.

Ken Sheridan
Stafford, England

Needsomehelp said:
Sorry to be such a pain...

How do I reference the query so vba knows I'm looking for [avail] in
[queryspaceavail]? I get an error "method or data member not found", and teh
RST.[avail] line is highlighted. My form is not based on this query.

My form is based on an individual child's record, and the information is
going into [sessioninfo], a subform that records the classes paid for. The
queryspaceavail sums up the classes purchased, and in that query, I have a
calculated field [avail] that is made up of the field [ttl inventory] - [sum
of classes purchased].


Thanks!



Lord Kelvan said:
ok iif is the if satatement for queries

if is for VBA

try

if recordset.[avail]>0 then
msgbox("good job")
{do the update query commands here}
else
msgbox ("don't oversell this class " & recordset.[avail] & " spots
are avaliable")
end if

....... actually i just realised what you are doing you are using bound
objects rather than recordsets.

i am not that familer with bound objects as i use recordsets all the
time they are more reliable and eaiser to control.

how are you storing the data for the classes are you having a max
value in one table and in the other table where poeple come in and
trying to compare thoes two values or are you subtracting a number
form that value till it reaches 0

if you want to control it you are going to have to use a record set

try this code in the after update
(note this isnt tested)

dim db as database
dim rst as recordset

set db = currentdb()
Set rst = Db.OpenRecordset("space available", dbOpenDynaset)

if rst.[avail]>0 then
msgbox("good job")
DoCmd.DoMenuItem acFormBar, acEditMenu, acSaveRecord
else
msgbox ("don't oversell this class " & rst.[avail] & " spots are
avaliable")
DoCmd.DoMenuItem acFormBar, acEditMenu, acundo
end if

hope this helps

Regards
Kelvan
Click to expand...
Click to expand...
 
Back
Top