Just a simple problem, any help?

[Roselyn]

Hi, I'm relatively new to the world of Access and I have a query. What the query I am trying to write is to basically count stock up to 20, I enter the details of the product which then adds to the database but how to i get it to stop at 20 and display an error message. I've tried several counters written into the code but then my insert statements wont work. Is there anyone/anywhere that can help me or show me some sample code to finish this?

Many thanks - Roselyn

 

[meboz]

If you are entering the data on a form, place a text box in the form header and use a Count function to get a count of the records underlying the form.

Place some code in the Form_BeforeUpdate event that does something like this.

dim intMBR as vbMsgBoxResult
If txtCount.value = 20 then 
    intMBR = msgbox("This entry will take the record count to over 20. Do you
                wish to continue",vbYesNo,)
    If intMBR = vbNo Then
        cancel = true
    End If

See how you go...

 

[WayneRyan]

Roselyn,

The answer depends on your table structure. Where (and how) are
you maintaining the quantity? Is it one numeric entry in your
products table? Or is it a summation of transaction records
for a particular product?

Need more info.

Wayne

 

[Roselyn]

Thanks guys, I got the message box to appear but I changed the conditions for it. With the table its not numerical it's a complete new record each time with ProductCode etc. about 4 fields. I want one Sub that will allow me to insert data as well as add it to a counter. I'm stuck because i don't know if there's a way to count records like an index. Any help(ideally some code ) would be much, much appreciated. Oh and is there any sites that have a nice tutorial sort of guide to programming?

Many thanks again

 

[WayneRyan]

Roselyn,

As for other sites, you can look at Microsoft's NorthWind sample DB.

If they are entering a quantity, you can use the BeforeUpdate event
to do a DSum calculation:

DSum("[QuantitySold]", "YourTable", "[ProductID] = " & Me.ProductID)

Then if the sum is > 20, present a MsgBox and Cancel the update.

Small step for now, but we're progressing,
Wayne