Please Help how to add auto reset

[XQuilet]

Hi and Greeting to everyone
i need help with the following and i have try my very best to try out every possible way to add the auto reset into it but how ever i am not able to do so and i am new to vba.

i learn by searching the web but i could not find any best way to do it. and i am learning thru the ms access db northwind

hope that someone could help me out, thanks in advance~!!

Question 1

AO No << table field contain Adjustment Order Number like AO14/02/0001 i need to reset this running number monthly
like example
AO14/02/0001
AO14/02/0002
AO14/02/0003

AO14/03/0001
AO14/03/0002
AO14/03/0003

Public Function nextIdString(ByVal nisFieldName As String, ByVal nisTableName As String, ByVal nisPrefix As String) As Variant

nextIdString = Nz(DMax("[" & nisFieldName & "]", nisTableName, "[" & nisFieldName & "] Like '" & nisPrefix & "*'"), nisPrefix & "0")
' nisPrefix & "0" gives you a default value if one is not found in the table

nextIdString = Val(Mid(nextIdString, Len(nisPrefix) + 1)) + 1
' Get next numerical value by looking at the highest value number after the prefix and adding 1

nextIdString = nisPrefix & Format(nextIdString, "0000") ' create next string Id
' Create new ID string by concatenating the formated number to te prefix
End Function

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strPrefix As String
strPrefix = "AO" & Format(Date, "yy") & "/" & Format(Date, "mm") & "/"
If Len(Me.[AO No] & vbNullString) = 0 Then Me.[AO No] = nextIdString("AO No", "AO", strPrefix)
End Sub

Question 2

i have 5 different type of Order table and a customer table and 3 of the order table is relationship to Customer table
and inventory transaction table is relationship to every order table
tbl PO = Purchase Order
tbl SO = Sales Order
tbl RO = Repair Order
tbl LO = Loan Order
tbl AO = Adjustment Order
tbl Customer = Customer

and each Order number from SO, RO, LO is relationship to Customer
meaning customer can have many order number from each order table link to it
after the relationship is setup to customer table

when i enter customer table it prompt me for subdatasheet. if i set up the subdatasheet i could only link 1 table to it
even with a union sql table it also happen the same thing

so i could only use the above function to and recreate a new table with different form to put all order into 1 order table

so hopefully some one could help me with it and i have attach the DB that i create. please take a look.

 

[CazB]

Quick Question: why do you have separate tables for each type of order? Could you not just have one table which contains all the orders, with a 'flag' on each record to say what type of order it is?

 

[JHB]

so hopefully some one could help me with it and i have attach the DB that i create. please take a look.

Your database/tables are totally wrong set up, you need only one table for the orders like CazB suggests.

 

[XQuilet]

i seperate the tabel in the first place because i have different kind of order number
example:
tbl PO = PO14/02/A0001 or PO14/02/U0001
tbl SO = SO14/02/A0001 or SO14/02/U0001
tbl RO = RO14/02/0001
tbl LO = LO14/02/0001
tbl AO = AO14/02/0001

but now i am trying to put it into 1 table and create different form to handle the Order number

but some what i need a reset to reset the last incremental code
for RO / LO / PO every month

by using the function i almost know what to do except i am stuck at how to insert the monthly reset coding into it

previous code i use was
'Me.[Pre Adj] = Format(Nz(DMax("[Pre Adj]", "AO", "Month(Record_Date) = Month(Date())")) + 1, "0000")
'Me.AO_No = "AO" & Format(Date, "yy") & "/" & Format(Date, "mm") & "/" & [Pre Adj]
which needed to create a extra table field to generate the incremental number

but after i found the function code and i feel it a good code to use in order to have 1 table for all order but i do not know how to add in the reset code to it

i have been stuck on searching the code and try and error for 2 days and still nothing come up. so i came over to the forum hoping that some for you could help me with it

please help me on the coding to add a reset to the function that i have.

i sincerely thanks you guys alot ^-^

 

[JHB]

When you have finish the work putting all orders in one table, then post your database again + information about how you decide which type orders an order is.

 

[XQuilet]

new setup
tbl = Order
Qry = PO from tbl Order
form = PO from PO Query with criteria like "PO*" to display all order with PO number and also to create new PO order.

now i want to use a single query, rather then to create many query to get other order type

is it possible to use the form name of form
that created by the query, to set the criteria
of the query that create the form

example

"Order Query" Create "PO Form"
"PO Form" name with wildcard "*" as Criteria in "Order Query"

i have also attach the database for reference, but not fully done up with other order type only PO order type is done

 

[jdraw]

I see these tables:
Customer, Contact, Employee, Order and you will have need for OrderDetails and Product.
You may also have need for Supplier, OrderType.
I don't think Inventory is a table itself as you have shown in your database.

I recommend you write a few sentences about your business to identify the major subjects. Normalize your tables.
I would not recommend attachment data type.
I would recommend you name your Id fields specific to the table they are in
eg CustomerID, EmployeeID

watch the video tutorials at
http://www.access-programmers.co.uk/forums/showthread.php?t=245998#14

 

[XQuilet]

Hi. Inventory table is my product table. And inventory transaction record every product transaction moving in and out of the store. And every transaction has a transaction number recorded in order table
And there is 5 order type:
Sales order
Purchase order
Repair order
Loan order
Adjustment order.

Each order from sales and repair n loan belong to a customer
Each order also has n employee recorded coz of the picking of stock I need to record who pick it
Each order from purchase and adjustment belong to store n purchase side so only has employee recorded.

There is noneed for supplier table because every product has it own supplier recorded directly to it as there is no supplier information to be recorded

What I am trying to do is to record the transaction in my store.
Sales order is generate by sales and purchase order is generate by purchaser so this 2 number I just basically key in.

And as for repair order, loan order, and adjustment order, is from my side n also need to generate the same number type as company requirement

Hopefully some of u could help me out with the code I need
previously I edit the northwind db to fit my need and it going well
But I find it troublesome so I want to remake a clear db to do what I need.

And I know what I doing with this new db.

I know many of u here are trying to teach me the right thing n showing me example n I know it very well n I will read it up.

But I really have the need to get the code to get my db up and running asap.

If not my stock will go haywire or missing

Please kindly help me out. And thanks a lot.

 

[XQuilet]

Please anyone? Could help me thanks