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 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 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?
i have a report that opens from a query that pulls from 8 different tables using the date as the primary key for each table. I want to be able to open my query without having to input a blank record for each date in each table. Basically I don't use every table everyday. Sometimes I just use 3-4 tables and others I will use all 8.
I run a query that I export out to a tab delimited file. This text file is used for another application. The problem I am having is if I select "skip field" in my text "export specification" it still exports out that piece of data, even though I have selected "Skip Field". The data type on this field is a number and field size is integer.
I thought I read in another access newsgroup that this is a known bug? Is that true? Is there a fix? I am anxious to fix this because, I have 90 query's that I export to text and I would hate to have to go to each query and select "Not Show" that field.
I want to copy data from one table to another: INSERT INTO Table1 (codeid) SELECT codeid FROM Table2 WHERE a=7
it works, but the problem occurs when diplicate entries occur in codeid of Table1 (because is is set to No Duplicates).
As a result no rows are inserted. It's OK, cause that's the way it should be, but I was wondering if there is a way to tell MS Access to skip insertion of values that will cause duplication. So the rows that are ok will be inserted and the ones that cause duplicates will be skipped.
I just don't want to go through the loop and insert row-by-row.
Currently, I have a button on my form that users can click on to generate a report based on the info from the form. Once the report has opened, they publish the form with MS Word for further edit if needed.
How do I set up the button so that it opens the report then automatically publishes the report with MS Word?
My form is always open and runs a timer event which runs a series of functions and procedures. I don't want to run a certain procedure on every timer cycle, but rather, every other timer cycle.
Any thoughts on how best to accomplish this. I'm having a mental block!
I have a form which displays the values of a table Vehicles. It has a control at the bottom of the form that allows you to toggle through the records, or enter a specific number of a record for it to jump to. It starts at 1.
I want it to skip the first 3 records in the table, and start at number 4. Is there a simple way to do that?
Is there a way to delete the borders from a form? You know the ones from the bottom and right hand side which have the buttons on for you to skip between records?
Also the one on the left which has the arrow in the top corner.
Put simply - when I do a find it displays the first account that matches it, then, when you leave the find screen and hit the "next record" key it simply displays the next account (i.e it no longer matches the search criteria).
So my question is, How do I search records, then skip through ALL accounts that match the criteria.
example: 100 accounts in a database. 50 accounts are assigned "Mr A" and 50 "Mr B". Mr A and Mr B sit down to work - hit find, and type in their name. The form shows 1 account, and when mr A hits next fifty times it should not move on to mr Bs (possibly a "no more matches/records" message?).
I hope this is clear enough - and thanks in advance.
ps - I heard something about one form to search - and a subform to display results - the search criteria is static in form one, where as the subform can be skipped ("next record" pressed). But trust me - Im inexperianced :p and would need a nice newbie friendly description of such technological witchcraft.
I want to combine six different memo fields into one. I found this code and it works to combine two fields so I edited to add a third and it does not do anything.
Many Regions, Many Towers, Many Countries and all..
Has to create a worksheet for Each Region-Tower and Paste the countries' records.
Private Sub Input_Click() Dim Mainrset As Recordset Dim Temp As Variant Set Mainrset = CurrentDb.OpenRecordset("Query_Form") Mainrset.MoveLast Mainrset.MoveFirst
[Code] .....
The above code is not correct as some Tower/Process are not associated with some countries. Usually What I do is to loop through all the records and look for the changes in the field. Is that the only way?
When I run the following sql (in a query) I enter information once for [enter cty id]. and it all works fine.
What I would like to do now, is write the vba code so that the sql runs multiple times looping through a list of items.
For instance instead of the popup window asking "enter cty id", I'd like to automatically have the code loop through a list. For example a list of 1 through 10.
Then, in the loop vba code:
after item 1 is run, do an export of the results. after item 2, do an export. etc.
Code:
select a.south_index_id, c.cnty_cd, a.southwest_name_source_cd, sp.south_ssn, sp.estimated_birth_dt, [a.LAST_NAME] & ', ' & [a.FIRST_NAME) PERSONNAME], a.BIRTH_DT, a.GENDER_CD, sp.ms_pmi, cnty_person_id FROM SW_alias_name AS a, SW_south_person AS sp, SW_county_pid AS c WHERE EXISTS (select * from SW_alias_name a2 where ucase(a2.last_name) = ucase(a.last_name)
I want to stop/skip appending of those records who match in main data table "MCIGMMS" on the basis [PORTCD] [IGMNO] fields in "PCIGMMS2". When Match found Msg Box appear and appending skip or stop of those records.
i've been working on a sample database today that someone has written some vb code into, which enables the database to merge with some dialler software. basically it's just a hang up and dial button on a form for surveying people.
i've done ok extracting the code for the dial and hangup buttons, referencing the external file and getting my test database to interface with the dialler but it currently allows multiple users to edit the same record, which i need to stop it doing.
the guy that's done the coding says he's more of a vb programmer and not much use in access (he's definitely better than me!) but i should be able to insert some code that will skip to the next record if the record is being worked on with someone else.
i think the piece of code that i need is
Forms("Form1").RecordLocks = 2
the only problem is i don't know what else to type with it or where in the code to insert it.
I have the following code to display what choice I make on a drop down box:
Private Sub cboMajorLocation_AfterUpdate()
Select Case Me.cboMajorLocation.Value
Case 1 Me.lstPC.RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like '*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;" Case 2 Me.lstPC.RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'FTM*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;" Case 3 Me.lstPC.RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'CS*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;" Case 4 Me![lstPC].RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'PQL*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;" Case 5 Me![lstPC].RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'Savage*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;" Case 6 Me![lstPC].RowSource = "SELECT DISTINCTROW tblHardware.HardwareID, tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description FROM tblHardware WHERE (((tblHardware.Location) Like 'Retail*') And ((tblHardware.Type) = 'PC')) ORDER BY tblHardware.Name, tblHardware.Assignment, tblHardware.Location, tblHardware.Description;"
End Select End Sub
What I need to know is how to make a statement that you can put in where you want it to display all but take out certain parts of it. Example: On "Case 3" it displays everything starting with "CS*" I want it to not display certain items like "Retail*", & "FTM*" & "PQL*" & "Savage*"
When writing a long line of code, I seem to recall there is a method of stating a new line, trouble is I cant remember what it is. I belive its something like an underscore. Can anyone help please?