I am having trouble calculating a field in a table and on a form.
I have the following Tables
CUSTOMER: Customer No, Name, Address, City, State, Zip, Discount, Phone, Fax, Contact Person.
ORDER: Customer No, Order No, Order-Date, Delivery-Date.
ORDER LINE: Order No, Product No, Quantity Ordered,
line-item-cost
PRODUCT: Product No, Description, Price, Product Image.
I need to calculate the line-item cost and need it to update every time the user enters a new Quantity ordered on the ORDER LINE form.
I know its (Quantity Ordered*Price)-(Price*Discount) and I did this is a query and it worked but I need it to update in the form and update in the table not the query. I have tried everything (Macros, Queries, and Formulas) and I just can not get it to work.
Does anyone know how to do this?
Thanks
I am having trouble calculating a field in a table and on a form. I have the following Tables CUSTOMER: Customer No, Name, Address, City, State, Zip, Discount, Phone, Fax, Contact Person. ORDER: Customer No, Order No, Order-Date, Delivery-Date. ORDER LINE: Order No, Product No, Quantity Ordered, line-item-cost PRODUCT: Product No, Description, Price, Product Image.
I need to calculate the line-item cost and need it to update every time the user enters a new Quantity ordered on the ORDER LINE form. I know its (Quantity Ordered*Price)-(Price*Discount) and I did this is a query and it worked but I need it to update in the form and update in the table not the query. I have tried everything (Macros, Queries, and Formulas) and I just can not get it to work. Does anyone know how to do this? Thanks
I am currently making a database project for a school assignment.
I have based the project idea on a local hotel, which consits of a diffrent price dependant on the date booked.
So i have set up a table called "prices" which consits of the following fields:
PriceID RoomID * Price Start Date End Date
the RoomID is the foreign key, as it is linked (one-to-many) with my Room's table, which consists of the following fields:
RoomID * Room Number .. (unecessary fields for the problem)
The room ID is also linked to the bookings table (again one-to-many) with the bookings table consisting of:
BookingID CustomerID RoomID Start Date End Date ... (uneccessary fields)
what i am hoping to do is (i assume) make a query that will calculate the cost the customer has to pay. So it will look at the start and end date booked in the bookings table and comapre it to the dates in prices table, and find out the cost from this....
any help on going about this would be greatly appreciated, and i hope i have supplied sufficient information (i'll try attach a graphical image of the relationships if that is helpful?)
Each new row of record needs to have their own line item number starting from 1 with criteria if order number is the same then carryover line item number on that record. I know how to get line item starting from 1 to last by assigning unbound control field with entry "=1".
Then I would use the SUM feature so the "group by" row appears.
Then I would build the expression above in an expression column. If that's correct, could you give me the expression typed "exactly" as you think it would appear? I'm getting syntax errors when I'm trying it.
And then, once I have the expression built, what should be the "group by" selection under each of the columns?
Sorry for being so ignorant. The rest of my data base works great... but I'm just not getting this one thing!
Access Database in which i download the table from an Excel file.I am looking to create a query with certain criteria but did not have any luck recently.
1) I will need the query to show <Short code> that are only unquie to Korea.That means shortcode with a count of 1 belonging to Korea only.
2) I will need the query to show as a single line item <Shortcode> that appears two times under the country Korea. For example short code CB01406 is shown as two rows, i will like it to only show as a single row.
3) For all other shortcodes which exist in China as well as in Korea or China only. I will like them to be hidden in the query. That means not shown at all.
I'm trying to have a command button send info on the form straight to Outlook as a meeting. So far, the code below works apart from the address bit. I need the code to automatically add the same 2 email addresses as recepients everytime.
Error message highlights the red line below with the following message:
Runtime error '438' Object doesn't support this property or method
I'm new to VBA, so don't understand what is wrong here. The reference is set, I've tried different permutations of the "To:" & "Recipients.Add" for the email address with no joy.
Code: Private Sub cmdSend_Click() Dim outApp As Object Set outApp = CreateObject("Outlook.application") Set outmail = outApp.CreateItem(olappointmentitem)
I have a main form with 3 sub forms. The main form is tied to a table called QUOTES_MASTER. The first sub form is tied to a table called QUOTE_ LINE_ ITEMS_DIRTGLUE. It calculates the subtotal when selecting items. The relationship is one-to-many linked on QUOTE_ID.
The second sub form adds up total of all line items and is not tied to a table.The third sub form adds ESTIMATED FREIGHT to the PRODUCT TOTAL and is not tied to a table. how to get the values from the line items form inserted into the QUOTE_LINE_ITEMS_DIRTGLUE table as they are added.
I also want to insert the total value from ESTIMATED DELIVERED into the LINE_TOTALS field in the table QUOTES_MASTER.I tried this code on the product total sub form but it doesn't do anything and there are no errors:
Private Sub PROD_SUB_AfterUpdate() DoCmd.RunSQL "UPDATE QUOTE_LINE_ITEMS_DIRTGLUE SET QUOTE_LINE_ITEMS_DIRTGLUE.SUBTOTAL = Me.PROD_SUB WHERE QUOTES_MASTER.QUOTE_ID = " & Me.QUOTE_ID
I'm adding to a DB which has been working well for a while.
I have a continuous form which has a query behind it. The query takes two values: (For example)
[LineCost] and [LineQTY]
Inside the query, we use the expression TLC: [LineCost] * [LineQTY]
I need to sum this so a Sale with several lines gives me the total sell and cost value.
If you look at the sfrmEditQuote - you'll see the exact same method - working. sfrmEditSale is direct copy of this form, with some changes here and there. But can I get past the dreaded #error? Nope.
I've tried renaming the fields and text boxes, checking the query, but I keep getting #error on the sfrmEditSale form footer.
I have a form that simply lists the items: DEPARTMENT_NBR and DEPARTMENT_NAME
In the table: DEPARTMENT_TBL
When I edit the field DEPARTMENT_NBR and it is in error (must be between 01 and 99) I want to put out a message in a MSGBOX and SetFocus back on the DEPARTMENT_NBR.
I coded the MSGBOX with vbokonly and then DEPARTMENT_NBR.SetFocus, but after the message displays and enter is hit for the ok the cursor jumps to the DEPARTMENT_NAME on the current line. Is there a way to get the SetFocus to work properly on repeating items like this? I can never seem to get them to perform the same as they do on non-repeating items.
Thanks for your help.
HERE'S THE EXACT CODE: If IsNumeric(DEPARTMENT_NBR) = False Then If DEPARTMENT_NBR <> "00" Then MsgBox "DEPARTMENT NUMBER must between 01 and 99.", vbOKOnly DEPARTMENT_NBR.SetFocus GoTo DEPARTMENT_NBR_EXIT (exits the paragraph skipping other checks) End If End If
Also, is there a way to look at a specific item in a list like that? IE. I want to reference the 3rd row's DEPARTMENT_NBR. Thanks.
If i simply remove the GROUP BY line and stick the semicolon at the end of the previous line (.EmpID; ) it works just fine. How is adding a group by line causing an error?I tried adding another parenthes at the beginning ((( and ending the joins as EmpID); and that failed with the exact same error.
I have a module which reads a CSV transaction file line by line and adds the correct transactions to an access table and places the wrong ones in a logfile.Now some transactions are rejected twice there is even one rejected six times. Whereas one wrong transaction is processed only once. I am certainly overlooking something obvious in the logic but what. Here is the relevant code.
Code:
Function ImportCSVForConfederation(inputCSV, ORG) Dim TNO As Integer, TACT As Integer, TABLE As String, TLINE As String, I As Integer, J As Integer, K As Integer Dim FLD1 As String, FLD2 As String, FLD3 As String, FLD4 As String, LogFile As String, LogPath As String Dim Lim As String, ITNO As Integer
I am having a little difficulty with my importing in Access. Every time I import my text file, the lines will be jumbled. I have been reading up and I found this recordset code that seems to be what I need:
Code: Dim strLine As String Dim intLineNum As Integer Dim MyDB As DAO.Database Dim rst As DAO.Recordset Open "C:TestTest.txt" For Input As #1 Set MyDB = CurrentDb Set rst = MyDB.OpenRecordset("tblResults", dbOpenDynaset) CurrentDb.Execute "DELETE * FROM tblResults", dbFailOnError 'Clear tblResults
[code]....
Basically, this code will extract data from the text file as long as it fulfills the Mid$ criteria. Here's where my problem comes. Each line in my text file is of different lengths and I have to capture the entire line.
I think using the Left$ function would work, but I don't know how to determine the character count such that the entire line of text would be inserted into the table.
Another difference between what I need and the code above is that, I am required to store each line into each row of my table, meaning
Line 1 is placed in Row 1 Column 1 Line 2 is placed in Row 2 Column 1 Line 3 is placed in Row 3 Column 1 . . . Line X is placed in Row X Column 1.
I can change multiple things on a line graph with VBA.
Me.Graph47.chartType = GraphType ' take 65 for line with me.graph47 .SeriesCollection(1).border.Color = vbblue ' change the line color .SeriesCollection(1).border.Weight = LineWeight ' change the line weight to for example 3 .SeriesCollection(2).MarkerSize = MarkerWeight ' Change the marker weight, for example 4 .SeriesCollection(2).MarkerBackgroundColor = vbblue ' Change the marker color, .SeriesCollection(2).axisgroup = 2 ' put this series on the secondary axis end with
SeriesColection(1) is line with markers. This is correct.But now I want the seriescollection(2) without line, so only the markers. I cheched the MSDN site from Microsoft. The Excel trick with the macro does not work for me.how to hide the line with VBA for only SeriesCollection(2) in Access?
Well at the moment i'm still in the design stages, but i made a database with test fields to see how it would work out best.
Basically i have to have a form with a drop down list for each type of computer componant and when selected the total price for all the items selected so far needs to show up at the bottom of the form.
Or when a button is pressed the price could be updated, that is fine too!
The way i was planning to do it was having an orders page, in which each field was a lookup to a difference table, one for each componant Processor; ProcID, Proc name, Proc cost
Anyone have any ideas on the easiest way to load the total value for the items selected? Would be much appreciated! Thanks!
I need to create a report that shows profit/loss totals for a vehicle that I have listed in a table named "Vehicle Table". I have four columns: Cost, Shipping, Promotional expense, Repair, that I need to total and then subtract from the sales price to show the profit. Then, divide the profit by different percentages to show what I owe each salesman. I need to keep the different columns seperate and be able to increase the dollar amounts in each column as a new invoice is received for each vehicle. For example, I have a $25.00 wash job in the "Repair column" and then I have to install a new set of tires for $450.00. The total in the "Repair Column" should now be $475.00. I can not make it add to the current balance in each of the certain columns. Would someone be so kind and help?
:( I am using a switchboard in my database, I have 3 forms using the same query,formats,etc but the 2 of 3 have filters. My question is how can I filter the form when I click a button in the Switchboard.
I tried to do docmd.applyfilter but when the form is open nothing happens.
And I also use the Form_Switchboard!Itemnumber = 2 but the only number it can see is with 0 value.
Please help me. I just don't know how to catch the field from Switchboard. Please help. Thank you so much.
I am having so much trouble with a order-line form.
I have a table that has the following information. The Table is Called ORDER LINE: Its attributes are, Order No, Product No, Quantity Ordered, line-item cost.
I need the line-item cost to automatically update when someone changes the quantity ordered. How would I go about doing this?
Now I am pretty new to SQL statements and I am assuming that this is how I would go about doing that.
I have a order system which allows customers to place items on. When an item is selected and a quantity has been entered and add button is selected, this adds the item into a listbox with all the details. Each item that is selected a quantity is selected, this would then work out a total price and this total would be displayd within the list box too. i want to add all the totals up for that order and have it displayed within a text box.
for example:
product ID, Name, quantiy, item price, totalcost P0001 Top 5 1.00 5.00 P0002 Top 4 2.00 8.00 P0003 Top 3 1.00 3.00
i then have a text box where i want to display the overall total for these items within. hope this helps. im really stuck on this one!!!
I need a query to help me track share of cost for clients. Anyone have any ideas? This is what I am working with. sorry it didn't paste very well. My problem is that my query keeps subtracking the share of cost and I want it to stop subtracking the share of cost once the share of cost has been met. HOpe that makes sense. Thanks in advance for your help in this matter. Angelflower.
Throught 01 year, I can make 10 purchases of pens with different prices.
I need to run a query of an individual department's consumption of pens in the month of June for example. I can get the right qty, no problem. But for $$$ purposes, I needed the query to return the most recent PricePurchased as compared to the OrderedDate.
Hi, I was wondering if there was an easy way to deduct VAT from ALL my records as I entered them with VAT, and now my boss is saying he wants them without VAT.
Is there an easy way to do this!? As there are thousands of records that I need to calculate the cost without VAT!!
Hi pals, I have the following problem. I have a form that runs a query from 5 tables linked via odbc. The thing is that the result is showed ok.. but when I tried to edit an item in grid, I can't do it. (get that noisy bell). I realized that if i only use one one table, I can edit ok.. but If I use more than one, I can't. Any solution to that?? Thnks Mariano
My main form has a list box - user selects an item in the list box for editing. The selected item displays in a text box on the subform where the editing is to be done. User than makes corrections to the text box and clicks a button to save the changes. I have all of this working. But I can't figure out how to update the main form on this save button.
What is happening right now is the main form is not updating at all and the original selection in the list box is still highlighted. If I click within the list box, the list box updates with the correct entry.
What I need to happen is when the save button is clicked, that the listbox updates and the selection in the list box is deselected and then set focus on another text box that is on the main form.
(I was doing this as 2 separate forms and the edit form opened after the user selected the item and clicked a button (kept the first form open but not visible and based the text box on the list box) This worked but again when the user saved and returned to the first form even though I was able to setfocus to the text box, the list box kept the selection highlighted.)
