2 questions regarding Back Orders on a database

[KenshiroUK]

Good eveing all, I ahve 2 questions regarding Access and didn't know where to post. Firstly, we have an Orders DB that also includes Back Orders, when a user opens up the orders I wish to flag up an message about the remaining orders "You have X Back Order(s) left."

I don't really know how to do this. But Could anybody help me out?

My second message is to do with that Back Order related to the Products, when an Order is placed in a drop down menu those Orders are linked to the Products table. Is there a way to include an exclamation mark or something to those remaining Back Orders on Products out of stock?

 

[eirman]

You should be able to do some of what you want with conditional formatting, however
we need a bit more information before we can effectively help you......

What version of access are you using?
What is the structure of the orders table?
What constitutes a back order compared to a regular order?
Ideally post a sample database with non-personal sample data

 

[KenshiroUK]

I'm currently using 2007, A Back Order is when the item is out of stock and the Order Can still be placed, but I have included a status dropdown in my form so the user can Change Status from Active to Back Order. Ideally I would also like a message prompting the user if they have not given an Order a Status as well.

'Please choose an Order Status' or something like that. I have also attached a copy of my Database. Most information has been stripped out and its just the basics.

 

[eirman]

Ideally I would also like a message prompting the user if they have not given an Order a Status as well.

This part is easy ....
Go into design in your "Order Details" Table, Click on the Order status field and at the bottom change Required to YES and Allow Zero Length to NO

It will then be impossible to create an order without filling in the status

I'll have a look at your other requirement later today

 

[CazB]

An other option would be, that you could automatically set the 'status' based on comparing the qty available to the qty ordered.... something like:

Private Sub Quantity_BeforeUpdate(Cancel as Integer)
Dim qtyavail As Integer
 
qtyavail = Me.Combo20.Column(2)
 
If Me.Quantity.Value > qtyavail Then
Me.[Combo30] = "Back Order"
Else: Me.[Combo30] = "Active"
End If
 
qtyavail=Null
 
End Sub

 

[CazB]

For the other part: you have a couple of options, but possibly the easiest is to set up a form which opens automatically when the database opens, which displays the message you want... and from which you can then load the form which you currently load automatically. if you see what I mean?

Have a look at the attached file...

 

[jdraw]

What exactly does this mean

an Orders DB that also includes Back Orders, when a user opens up the orders I wish to flag up an message about the remaining orders "You have X Back Order(s) left."

What differentiates an Order from a BackOrder? I note there is no BackOrder table as such.
Where do you get your VAT values from? Just curious.

 

[KenshiroUK]

What exactly does this mean


What differentiates an Order from a BackOrder? I note there is no BackOrder table as such.
Where do you get your VAT values from? Just curious.

There is a table called Order Status, which is linked to my Orders Form. When a Item is picked it displays quantity left. You can then set the Order Status to Active, Back Order, Cancelled Refunded, Done.

As for the VAT, this is a simple sum method based on the Sub Total values.

 

[KenshiroUK]

For the other part: you have a couple of options, but possibly the easiest is to set up a form which opens automatically when the database opens, which displays the message you want... and from which you can then load the form which you currently load automatically. if you see what I mean?

Have a look at the attached file...

Thank you for doing this. I have however moved the query and form to display the message across to my main database and the Message does not seem to be appearing when I open up the database.

 

[CazB]

Ok, if by 'moving over' you mean you imported them, then you'll need to go into your database options and change the form that's displayed on startup

 

[eirman]

This should do most of what you want ...

You will see the total number of back orders and you cannot leave the field blank


EDIT:: Delete that field with the #Name? error .... I forgot to!