Modules & VBA :: Invoice And Subform - Add Multiple Record (Item) At Once
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.
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.
I want to make an invoice with products and services and totals, the thing that has me wondering is how do I save an invoice with multiple lines in the item section of the invoice. I want to be able to choose a customer and then see the list of invoices connected to that customer, and when I click the invoice to open it as it was. What would be the best way to do this?
The invoice contains:Customer info, adress, name...etc
List - multiple lines of products sold / services completed Totals, date & time...etc Relationship goes like this: Customer -> Invoice <- Inventory / Services
I am creating a form that will require entry of first names, last names, and ID's of either none, or any number up to 15 people involved in a particular project. I am trying to accomplish this with a subform that links to the main form using the main forms primary key.
When done, you will be able to enter data into the record (project name, date, etc), go to the subform and enter the Primary Key (an AutoNumber), then the 0-15 names & ID's into the subform one netry at a time, with all entries linked via the primary key to that particular record. One problem is that the subform always displays the info for the previous records. I also want to be able to "get out" of the subform and back to the main form saving all subform data along with that record. (am I making sense?) and have the subform reset back to blank for the next record. I am completely lost and don't even know if I am approaching this in the right way. Any help would be greatly appreciated!!
I have a contineous form with a combobox for each record and i have a button to select the first item in the combobox then to refresh the list in the combobox .I want to creat a one button to select the first item in the combobox then refresh the list then go to the next record and select the first item on the list
I have a query pulling data from two other queries (qry_Reports and qry_Surveys). Clients may have more than one ReportID, but only one ClientID. I need to query for only the most current ReportID (which is the larger value) for each client to find the surveys for the most recent report. How can I query for only the most recent report for each client based on the highest value of the ReportID per ClientID?
I'm currently working on a database which requires invoicing as a part of it. The invoicing is done based on quarters, and I want the users to be able to use a multiple items form, listing all of their clients, to create the invoices. Each invoice must be created individually so they can be e-mailed to the client, and saved to the clients folder. So I was wondering if it would be possible to create individual invoices for clients using a multiple items form.
I'm trying to get a combo box to populate the box next to it.
Basically pick a project number in the 1st box and have the project name show up in the second box.
I have it working, but the problem is that when it puts in the name of the project, it fills the whole column with that name, and not just the one box.
Here is what it's doing: http://img156.imageshack.us/img156/2659/dbhm4.jpg
Here is the code im using.
Private Sub ProjectNumber_AfterUpdate() On Error Resume Next
Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String
ProjectNumber.SetFocus If ProjectNumber.Value > 0 Then strSQL = "SELECT * FROM ProjectList WHERE ID = " & ProjectNumber.Value
Set db = CurrentDb Set rs = db.OpenRecordset(strSQL) If Not rs.BOF Then Me.ProjectName = rs("ProjectName") End If rs.Close Set rs = Nothing db.Close Set db = Nothing End If
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database Dim FocusBln As Boolean Private Sub Identificeer() Me.[Datum Aangemaakt].Visible = True Me.[Datum Aangemaakt].SetFocus If Me.[Datum Aangemaakt].Text = "" Then
I am trying to build a sales invoice for a pharmacy that contains a sub form in a datasheet view. The main form contains only the invoice number and date while the sub form contains the drug code, drug name, Qty., Available Qty. and Updated Qty.
Presently, I can only code the update to takes place on each line of the datasheet. I want the update to take place at the end when I must have finished inputting all drugs bought with same invoice number.
I'm trying to create an invoice system with a sub-form within the main form. The issue I'm having is that the data in the sub-form keeps getting overwritten each time a new record is created for the main form. So when I go back to edit an older record, the data in the sub-form is changed to whatever the latest record had in it. Is there a way to lock in the data in the sub-form so that it stays with it's own record?
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)
My current database creates an invoice per order. Until now that worked wonderful. Now i want to create an invoice per month per customer with multiple orders on the same database. So i will have to create a new table/query on the existing order table.
Is it possible to display, in either a form, multiple images that are dynamically linked? Can they all be displayed at once 1 to many images, either by using navigation bars or scrolling through a subform? The links would be stored in another table within the same database and not as hyperlinks.
Conceptually it would be like scrolling through multiple records on a subform with images rather than alphanumeric data in fields.
Would like to know if this is possible and if so in what versions of ACCESS.
Is there add-on software that would make this possible.
I have 5 subform named "bene_subform1", "bene_subform2", "bene_subform3", "bene_subform4", "bene_subform5", and i would like to know if it's possible to msgbox the record from the beginning to end of record in sub-form 1, loop thru all the 5 subform via VBA?
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.
I have a report, based on two tables which I have combined into a query.
The first table is the invoice date & ref, and the second table is the invoice amounts & descriptions, which could be more than one.
I have based the report on the underlying query, and I want all the many invoice & amounts to appear on one invoice, but I get one invoice for each of the many sides of the relationship.
I have a related key field in each table, with a common ref and referential integrity and cascade update set, but it still produces one invoice rather than a combined.
I am working on a volunteer organization's database, and am trying to create capacity to print an invoice for a donation pledge. I have a sub-form that displays donation records for a selected individual. From the sub-form I have created a command button with an OnClick event procedure with the following code:
I'm having trouble with a text box, which is trying to sum some date in a subform. All I get is #Error, yet I'm sure I'm referencing the item in the subform OK. I've attached the database as it's self explanatory (I stuck a label on it).
The form I'm having trouble with is frmPurchaseOrdersDataEntry and you'll see what I'm getting at from there.
I am creating a database for a local mechanical garage, most of it is setup but i have an issue creating invoices, I need to create a button on a form which will create an invoice per record. Trouble I am having is if i have 1 record it will show just 1, but when i add multiple records they all show on 1 invoice sheet. It doesn't separate records, i but instead it adds the extra records before the page footer :/ causing 1 footer for many records. How to finish the functionality of this DB, i can certainly finish the design side. Everything in this DB is set accordingly, just the invoice system to do i believe, could some1 have a look and let me know? i have tried to upload it here but cant so its on this link [URL] ....
I am looking to check 3 sub forms for values and if they contain any then delete them. After a bit of googling I discovered that you should use recordset.recordcount - to which I tried but I get method or data not found - is this a valid method?
My code to which Im using is:
If Me.NoneChargeable_Admin_subform.Recordset.RecordCount >= 1 And Me.NoneChargeable_Manufact_subform.Recordset.RecordCount >= 1 And Me.NoneChargeable_Research_subform.Recordset.RecordCount >= 1 Then DoCmd.RunSQL "DELETE NoneChargeable_Admin.*, NoneChargeable_Manufact.*, NoneChargeable_Research.* " & vbCrLf & _ "FROM NoneChargeable_Admin, NoneChargeable_Manufact, NoneChargeable_Research;" End If DoCmd.Close acForm, "NoneChargeableHrs_frm", acSaveNo End Sub
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?
I am trying to create a form for a query so that I can filter on a chosen record and have the data presented in a more reader friendly format: the idea being that I have the common data show once in the form header and the rest show in the detail section (kind of like a form with subform type)
First I tried using the form wizard to create a Tabular layout form which I could have easily customised, but I am unable to use this because I get an error message telling me the wizard cannot create my form due to me choosing too many fields.
In my next attempt, I elected to use the Multiple Item form option, which seems to work OK but presents the following problems:
1) It has not included all of the data fields in my query... though it looks like I can add these manually 2) I am unable to reorganise the text boxes and labels the way I would like, as they all seem to be locked together Is there a setting somewhere that I can change to stop this from happening?
I wanted to auto populate an invoice record with same values as previously ordered by that same customer. I don't want to use default values as every customer orders different things.
BUT almost all customers make REPEAT orders of exactly the same things they ordered previously.
So...for example, can I search the invoice records by customer ID/Name and copy all the data from their previous into a new invoice record. I guess I could do this with an append query?
But will an append query run when I just click on 'new record' in the invoice form? Maybe there is a property '.on new record'...open append query....?
