Modules & VBA :: Incremental Invoice Number Using DLookup
Jun 8, 2015
Part of my access application does invoicing. I have an invoice number table that wholes one field "InvoiceNum". On my invoice report i have the following code:
Code:
Private Sub Report_Open(Cancel As Integer)
'// lookup invoice number when invoice opens
intInvoiceNum = Nz(DLookup("InvoiceNum", "tblInvoiceNum"), 0)
End Sub
[Code] .....
Problem: My report open with groupby ClientID and ProjectNum. Multiple invoices print on the same report. If any invoice spills over to second page the Format Code runs again inserting an incremental invoice number to the second page. How can i prevent this from happening?
View Replies
ADVERTISEMENT
Sep 10, 2006
Well, here's my database :
Name : String
Address : String
I want to add an automatically generated column, "no.", to show which number it's in. Like this :
No. Name Address
1. A Unknown
2. B Unknown2
The problem is, I can do that with Auto-Number, but if the user delete row 1, the number in row 2 is still 2, not 1.
Is it possible to generate query that have "no." column in it ?
View 1 Replies
View Related
Nov 6, 2014
I am working on an access database for my team. We keep and maintain a trail log for different departments.
The person will send a trail sheet to us we will input the info and attach what we call Production Instructions Number in this format (YEAR-Month-001), the next trail will get (YEAR-Month-002), the last 3 digits will start over when the month changes, I would like the PI number to populate automatically when the info is inserted.
View 11 Replies
View Related
Aug 28, 2013
I have a Primary Key field named Member ID(Number format) in a Table named Mail List. The field is populated with existing ID numbers and my need is to use my Member Entry Form to increment by 1 from the last entry.
I have gleaned from other references that this can easily be achieved by the following entry in the Default Value of the property sheet for the relevant field;
=DMax("Member ID","Mail List")+1
The Form saves without error but when I attempt to add a new record in the entry form, the ID Number reads "#Error".
View 14 Replies
View Related
Jul 1, 2013
Access 2010
I am trying to check for when a user trys to enter a duplicate number.
The control that I am checking is in a subform on the main form:
Main: frmCandidateInfo
Sub: frmTestInfo
Control on the subform is: RankOrder
I am trying to check the control entry against the table entry:
Table: tblTestEvents
Field: RankOrder
Here is what I have:
Code:
Private Sub RankOrder_BeforeUpdate(Cancel As Integer)
Dim lngRankDup As Long
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!frmCandidateInfo!sfTestInfo!Form!RankOrder, 0))
If lngRankDup <> 0 Then
MsgBox TestEventID & " already exists in the database"
End If
End Sub
I know that the error is due to the argument not being correct, but I am not sure how to fix it.
I have tried:
Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Me.RankOrder, 0))
Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & [RankOrder], 0))
Code:
lngRankDup = Nz(DLookup("[RankOrder]", "tblTestEvents", "[RankOrder]=" & Forms!sfTestInfo!Form!RankOrder, 0))
But nothing seems to work.
View 3 Replies
View Related
Jun 2, 2014
I'm trying to get an invoice number field to auto generate the next number, keeping the format as "00000"...this is what I have, which gets the next number but drops the leading 0
Code:
Private Sub Customer_AfterUpdate()
If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1)
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub
invoice numbers are 04024, 04025 etc...how I keep the formatiing?
View 5 Replies
View Related
Dec 4, 2013
I am trying to make a DLookup function to return the ID number of an entry that matches 2 or 3 criteria but I am struggling to get the syntax correct for the second and third criteria.
Here is what I have so far:
1 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] =" & Forms![Mainform]![p11] )
2 criteria, works fine =DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " And [timedata]![BGSnum] = 1001" )
BGSnum is a numerical value but it changes for each form I load, so what I want to do is use the form location value as the criteria.
E.g.
=DLookUp("[timedata]![id]","timedata", "[processdone] = " & Forms![Mainform]![p11] & " AND [BGSnum] = ' " & Forms![Mainform]![BGS] & "' ')
and possibly a third criteria too. This is where I am having problems and the syntax is wrong so MS Access wont let me save the macro.
View 6 Replies
View Related
Mar 17, 2014
I am working on a form and need it to be able to generate a random number to use as an invoice number. The only parameters i need it to meet is that it is at least 5 digits in length. I was hoping i could use a button to make the number generate in a separate box.
View 5 Replies
View Related
Oct 3, 2014
I work for an insurance company where various (and multiple) discounts or loads can be applied to a quoted premium for one reason or another.
These discounts/loads are stored in a table with a corresponding customer ID, where each row represents an individual discount/load (labelled and ordered-by a 'Step').
Unfortunately, the resulting premium from these discounts/loads is not stored in the database and are calculated on the fly by the front-end.
I have a requirement however, to store the 'new' premiums based on the stored discounts/loads for a report.
My problem is that the calculations must occur incrementally one after another, where the discount/load at each 'Step' applies to the resulting premium from the previous calculation.
The attached spreadsheet is demonstrative of the existing table where two additional columns have been added to show you what I need to calculate. 'Price_Amount' represents the discount/load in monetry terms relative to the calculation and 'New_Prem' is the premium resulting from the calculation step.
Notice that each discount/load applies to the previously calculated premium rather than the original one (Original_Prem = the starting point from which all further calculations should apply).
The full table would include many more rows for different customer ID's, where the number of 'steps' could be as few as 1 or as many as 7 per customer ID (ordered by 'Step').
Having toiled for many hours in Access to achieve the above, I am now resigned to the fact that only some kind of VBA function will achieve my requirements.
This function should take the Original Premium for each CustomerID and loop through each 'Step' applying the relative discount/load based on the value in 'Pricing' and the number type in 'Price_Type'. Once the function has calculated each step per CustomerID and has reached the maximum 'Step', it should move on to the next CustomerID.
such can only articulate the requirements without being able to convert this into actual coded logic. Nonetheless, I am in a bit of a pickle with this one and am under some pressure to create the report.
View 7 Replies
View Related
Jan 15, 2007
I have a small .mdb for invoicing. Till now I had to put invoice number, which had two parts, manually for the first part, for example 001 or 002 and automatically for the second part as Date part. So I had, for example 2006 or 2007. The complete number was, for example 001/2006 etc.
The problem was New Year. Now, all previous invoices from 2006 have 2007 extension and the first parts are not starting form 001 but they continue.
View 10 Replies
View Related
Sep 20, 2005
I am trying to incorporate a field in my customer order form for "Invoice Number". Not every customer order form will have an invoice number.
The Invoice Number field will default as null, or blank. I want to make it so the Invoice Number gets automatically populated ONLY IF another field on the same form is populated with "X".
Also, I would like the Invoice Number to equal the Previous Record that has an invoice number + 1. For example, if the previous 5 client records did not have an invoice number (blank because the client's form did not have a certain field populated with "X"), but the previous 6th record had an invoice number of 1001, then the current invoice number would be 1002.
So far, I just have the Form for the underlying table created. I don't have the invoice field in the table created yet. Not too sure where to start.
Thanks much appreciated in advance.
View 3 Replies
View Related
Oct 22, 2013
I have 3 tables invoice, customer and items. I need to create a query that will get the next invoice number from the invoice table and mark that invoice number in the items table for a set customer where the invoice field in the items table is blank.
The invoice table is now set up as a autonumber, therefore I need to append to get the last new number.
The items in the item table are unique and this is why I want to mark the invoice number in the item table field invoice.
View 1 Replies
View Related
Dec 24, 2014
I am selling some products, namely A to M. I record the orders of costumers in a table, named "orders". For example, if costumer "Jack" has ordered as the following:
Code:
ID costumer product quantity unitprice orderdate
12 jack A 4 10 25/11/2014
13 jack B 5 5 25/11/2014
14 jack C 1 4 25/11/2014
15 jack F 3 2 25/11/2014
16 Jack G 4 8 25/11/2014
17 Jack H 1 7 25/11/2014
Then I make a report based on the above table and issue that as the invoice to the costumer, who is jack here.
How can I make a unique invoice number, which automatically increases sequentially for each invoice? Do I need to add a new column to the table? I also need to be able to look for a specific invoice based on the number, or date.
View 11 Replies
View Related
Jan 7, 2015
I just came across a problem that I didn't recognize before. I had an invoice table and corresponding form that had an autonumber for the invoice number. The problem now is that I would like the invoice number to begin at 256 and then increase incrementally, because I had some previous invoices from my books. How to go about doing this.
View 5 Replies
View Related
Jul 1, 2013
I have created invoices as reports but now I'm looking for a way to add an invoice number to it. It needs to be an auto increment number for every invoice for every client.
I have 10 different clients and I want them to have their own incremented invoice number. Every client has it own unique number. So for example in week 1 I want to have the following invoice numbers; 01-0001, 02-0001, 03-0001 etc. In week 2 I want them to be; 01-0002, 02-0002, 03-0002. It is important that every client has the invoice numbers without gaps.
What is the best way to realize this?
View 2 Replies
View Related
Jun 20, 2014
I am trying construct a form that will only show an invoice if all the invoice records have been approved. I have created a form that will allow the user to look at each line of an invoice and has a checkbox to approve it. If the user has selected all the boxes it will disappear from the Approval view, which is a query that shows unique values.
The problem is that if all of the lines but one are checked it will also show up in the view for the unapproved invoices AND the approved invoices. I would like to know how to structure a query that shows unique values but ONLY if ALL of the invoice line items have been approved.
View 9 Replies
View Related
Jul 30, 2013
I have a main form and subform. The main form is bound to a table of invoices and the subform to a table of invoice items. I'm picking up the invoice number from the form to save it to the invoice items table, so I need to save the record for the invoice before saving the invoice items from the subform.
View 4 Replies
View Related
Jan 19, 2006
I have to add an invoice how can I do it.
Desgn of the ms access form is
Date (suggest me)
ShipMode (option button)
Buyer (combo)
Supplier (combo)
Indentor (combo)
Product UnitPrice UnitOfMeasurement Quantity
(combo) (textbox) (combo) (textbox) (btnaddrow) (btndeleterow)
(btnAddInvoice) (btnCancel)
Please suggest me how to do it.Any code or sample like this.
I don't know how to add new row of product e.t.c.
Please help me as I have never done any programming in ms access before
(Done most of web development,asp.net,jsp,coldfusion)
View 1 Replies
View Related
Oct 10, 2012
I am practising making relational databases as I haven't used them before.
The mock database I am creating is based around a cleaning company.
I am trying to make a form which will display all of the bookings a certain cleaner has in the future. I want it to be able to display a booking along with the services (Windows cleaning, vacuum cleaning etc...) the customer wants.
At the moment, it is displaying each service (Window cleaning, vacuum cleaning etc...) as separate result on the form and it is not combined. They both have the same booking_ID from the booking table. How can I combine them?
It is far easier to understand what I mean by looking at the database (Attached). Take a look at the form I have created and then click the > arrow at the bottom to see what I mean.
Test DB.zip
View 1 Replies
View Related
Feb 9, 2007
Hello,
I have had a search through the forums but cannot find anything that is relevant to my issue. Hope someone can help.
I have a table with scores and then grades
tbl_scoregrade
Score,Grade
-999,10
133,9
239,8
342,7
449,6
570,5
709,4
785,3
862,2
917,1
I want to reference a field called "score_result" and generate a grade from my grade table and place it in a field called "grade".
In excel I use the following formula, but it does not work in access.
=IF(ISBLANK(C51),0,VLOOKUP(C51,Scorecard!H7:I13,2, 1))
Is there a way of doing this in Access?
View 1 Replies
View Related
Mar 23, 2014
I almost complete my mini project - Stock Inventory:
To track Items enter and exit from Videoshop or simply known as Stock Transaction (IN/OUT). This also include a Inventory Catalog which basically show all the Items in the Videoshop, each Items details including category, transaction type description and most importantly quantity on hand.
As mentioned above, I almost complete the database except the most important Form - New Stock Transaction where User enter all the necessary details to issue an IN or OUT Transaction. To make things easy to understand, it share many similarity with Invoice and New Order.
Code:
Main Form (Table: StockTrans)
Trans_ID
Trans_Type
Trans_Date
Trans_Cust
Trans_Note
Code:
SubForm (Table: StockTrans_Items)
Trans_ID fk
ItemID fk
Trans_Item_Qty
When I finished records entry and enter Invoice completed button then it will auto update the Item Quantity on Hand.
View 1 Replies
View Related
Jan 26, 2014
I have been working on a query in my database and I would like to look up a Number Value from another table based on a Number ID field and the Number Date in the Number Values table, where the Number ID field in the table agrees to the Number ID Index field in the query, and the Number Date field in the table agrees with the Initial Date field in the query. The Dlookup syntax I currently have is as follows:
DLookUp("[Number Value]","Number Values","[Number ID]=" & [Number ID Index] & "And [Number Date]= [Initial Date]")
The Dlookup shown above does not return an error, but it returns a blank field. I know that individually, the Dlookup for the Number ID works, but when I add the And for the Number Date the Dlookup returns a blank. Also, I have verified that there are existing records where the Number Date and Initial Date fields agree.
Also, I changed the format of the date fields in the tables to be ShortDate, thinking that the Timestamp was causing the Dlookup to not find a match. I also tried to use the DateValue() function within a query to reformat with not luck.
Why my Dlookup is returning a blank?
View 3 Replies
View Related
Oct 8, 2013
Occasionally a user of mine needs to reset a sequence number in a table.
A few months ago I made him a simple app that has two text entry boxes and a button. The first text entry box uses a dlookup:
Code:
=DLookUp("sSessionNumID","qryGetAPIsessionLastValue")
...the other textbox ties to an update query, which updates the sequence number with the new value supplied by the user. Simple.
Then about a week ago it stopped running the dlookup part - nothing shows in the text box that is to show the current sequence num.
Oddly, if I enter a value in the update text box and press the button to update the sequence number (which still works) -- the previously dormant dlookup textbox now shows the new value - so it works ...it just doesn't want to work on this one workstation unless the update query is run first.
What can this be? The update button merely calls a DoCmd.RunSQL with both an insert (storing old value locally in ms access table) and an update (updating the sql server table's seq number using an ODBC DSN and sql server driver).
It's almost like the dlookup falls asleep now and won't wake up until the update is run...
View 2 Replies
View Related
May 29, 2015
DLookup function. (this is for a stamp collection database).
On my form ("InventoryInput") I have a text box called "Catalog" for a numeric entry and a text combo box for selecting a "Country" in drop down list.
I want to query a table called "CatNameList" to get the "StampName" of the item (based on the entries of Catalog and Country) and populate that name in the text box. The fields in this table are called "StampName", "CatNumb" & "CName" respectively.
I have successfully placed the following expression in the control source of that textbox and able to populate the StampName I need based solely on the catalog number alone.
That express is :
=DLookUp("StampName", "CatNameList", "CatNumb = Form![Catalog]")
So it will populate the "StampName" data to match the "Catalog" number entry just fine.
However, I need to add a second layer to incorporate the Country.
Example : There is a catalog "1" for "USA", and a catalog "1" for "Canada" but both have different "StampName".
I have been attempting to get that second piece added with no success. Here is the expression I have been trying to get to work :
=DLookUp("StampName", "CatNameList", "[CatNumb] = " & [Catalog] & " And CName = '" & [Country] & "'")
Right now, the text box is just blank with the above expression. I thought it may be because there was no match found, but I have triple checked to ensure I have the spelling correct on the country name in both places.
Basically, I just need the dlookup to take the "catalog" and "country" off the form and match it to the "CatNameList" table fields of "CatNumb" and "CName" to give me "StampName" field back on the form.
View 4 Replies
View Related
May 29, 2015
Having problems getting dlookup to work in the control source field of a text box.
My form has fields : Catalog # (numeric value) and Country (drop down text selection).
I would like to query a table CatNameList for a name (text) if the catalog # and country find a match on the table.
My field names on the CatNameList table are : Name, Number (to validate against the Catalog # entered on the form) and CName (to validate against the Country drop down on the form).
I am successfully able to populate the name from the CatNameList table on my form using lookup of the catalog # using this :
=DLookUp("Name","CatNameList","Number = Form![Catalog #]")
However, I will eventually have several catalog numbers that will be identical in the table CatNameList, thus why the country is important as the second criteria to be added into the dlookup.
I have tried for a few hours unsuccessfully to add the second portion to my dlookup.
This is what I have currently (not working) that I have been playing with, I'm sure I'm missing a quote mark, & or something simple.
=DLookUp("Name", "CatNameList", "Number = Form![Catalog #] And CName = ‘”& Form![Country] & ”’”)
View 14 Replies
View Related
Oct 7, 2005
one of my tables has a field called # of Days. the default (minimum) value for this field is five (5), which i set. however, if there is a case where five (5) days are not enough, the next value would be ten (10) since the permit is issued for 5 days at a time, and so on. can someone suggest a validation rule that only allows increments of 5, but the minimum value is never less than 5? thanx in advance! :)
View 1 Replies
View Related
