Hi.
Hope this is in the right forum, apologies if not.
I am sure this is a simple question, but if I have a long line of code, I have to keep scrolling to the role to see it on the screen.
How do I see the line of code so it appears on the screen without scrolling...i.e Word Wrap sort of thing.....?
Many thanks.
Frank.
Hello,
I am writing an IF Else code and need to go to a new line as the If IsNull.....are too many. Any idea how can I do this?
I get an end of statement error.
Thanks.
Hi All
I have the following code and wish to change the Weights.Collected field to True from false in the new query (expr3: ) in the new query.
Thanks
Private Sub cmdBuildQuery_Click()
On Error GoTo Err_BuildQry
Dim strSQL As String
Dim qdf As DAO.querydef
strSQL = "SELECT TOP "
strSQL = strSQL & Me.txtNumberToGet
strSQL = strSQL & " weights.Weights, Weights.DocketNo, Weights.Collected, Weights.UKBulk, Weights.weighttime FROM CollectionQry "
strSQL = strSQL & "ORDER BY ([id]);"
DoCmd.DeleteObject acQuery, "qryCollectYorks"
CurrentDb.CreateQueryDef "qryCollectYorks", strSQL
DoCmd.OpenQuery "appendUkBulk"
DoCmd.OpenQuery "qryCollectYorks"
Exit_BuildQry:
Exit Sub
Err_BuildQry:
If Err.Number = 7874 Then
Resume Next
Else
MsgBox Err.Number & " - " & Err.Description
Resume Exit_BuildQry
End If
End Sub
Is there a way to comment out a specific section of a SQL query within MS Access? The normal SQL commands such as, rem text; ' text ; - text ; or /* text doesn't seem to work.
Any help is appreciated.
Thanks.
Greetings,
I was wondering of I can simplify this line of code which to me looks very clumsy. It work but I assume it could be more efficient?
The line is;
If IsNothing(Me.transvalue) Or IsNothing(Me!FrmDestination.Form.sumtransvolume) Or Me.transvalue > Me!FrmDestination.Form.sumtransvolume Or Me.transvalue < Me!FrmDestination.Form.sumtransvolume Then
(obviously it is on one line)
The whole code is
If IsNothing(Me.transvalue) Or IsNothing(Me!FrmDestination.Form.sumtransvolume) Or Me.transvalue > Me!FrmDestination.Form.sumtransvolume Or Me.transvalue < Me!FrmDestination.Form.sumtransvolume Then
MsgBox "There is a volume error " & vbCrLf & _
"the data entry has not been properly completed " & vbCrLf & _
"Please correctly enter or check all Data", vbCritical, gstrAppTitle
Else
etc.
Point two is there a way of simplifying the "<" and ">" to say not equals.
Thanks in advance
rbinder
hello,
okay, simple question. How in visual basic code do you tell the prompt to go to the next line in the same control on a form? In other words, how do you mimic the behavior of the enter key in code?
D
I saw this code and was wondering how does it actually work?
I saw it in/connected to a textbox.
Private Sub savetime_BeforeUpdate(Cancel As Integer)
End Sub
I have manipulated the duplicate record code found online to suit my purposes. It works for all the other subforms, however this subform has too many fields so the code goes onto the next line. How do I solve this? Online it says using "& _ " but I can't get it to work. There's basically too many fields to fit on one line so I need to continue the code onto the next line
Code with end of line problems highlighted in red:
If Me.[subformEmissionsControlTarget].Form.RecordsetClone.RecordCount > 0 Then
strSql = "INSERT INTO tblEmissionsControlTarget ( System_ID_No, EmissionsStandard, OtherEmissionsStandard, IntakeThrottle, OBDrequired, OBDVersion, DPFregenmethod, ExhaustTemperature, EGRTemperatures, EGRRates, SteadyStateCycle, SteadyStateEngineeringLimitType, SteadyStateLegislativeLimitSmoke,
[Code] ....
I programming a eBay upholder, and having an issue with the description, as there cant be any line breaks in the column. I have little knowledge of Vba programming. I think I can use the replace command to change the line breaks to spaces.
View 4 Replies View RelatedI have a database that I've split and have deployed to 7 persons.
The thing is, I have three (3) forms:
Form 1
Form 2
Form 3
...that require changing a portion of a line in each form's respective VBA codes. I need to change the directories to where an excel workbook is stored after exporting, and another is where same excel is moved to after being uploaded.
At the moment, during deployment I have to manually change the directories.
I was thinking of maybe a pop-up form with four (4) fields that allows the user to select his/her name at the start-up of their frontend app, and paste the directories in the other text boxes provided.
This information will then be used to replace the directories in the VBA.
If I were to make changes and have to redeploy, I would have to do everything manually again.
Here's the statement
Code:
sqlfinal = "SELECT Employees.ID, Employees.Name "
sqlfinal = sqlfinal & "FROM ((qryDeptVBA INNER JOIN qrySkillVBA ON qryDeptVBA.ID = qrySkillVBA.ID) "
sqlfinal = sqlfinal & "INNER JOIN Employees ON qryDeptVBA.ID = Employees.ID) "
sqlfinal = sqlfinal & "INNER JOIN qryAreaVBA ON Employees.ID = qryAreaVBA.EmpID "
sqlfinal = sqlfinal & "GROUP BY Employees.ID, Employees.Name;"
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
[code]....
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?
Works great, but when I hit the number "3", (3 times in row) it will let me into the form. I want it to not let me in IF I don't know the password.
Where did I go wrong?
Private Sub Form_Load()
Dim pw As Variant
If InputBox("What is the password?", "Password") = "1" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
If InputBox("What is the password?", "Password") = "2" Then
Else
MsgBox "Invalid Password", vbCritical, "Sorry Charlie"
DoCmd.Close
End If
End If
End Sub
I protect my code from people being able to read it by setting a password on the code from Tools > Properties, selecting the Protection tab and entering a password, and clicking "Lock Project"
Is there a way to write code that will remove that Lock Project check and check it back on?
I've looked through the Application.SetOption command and it doesn't seem to be one of the choices. It would be very helpful if someone knew how to do this.
Thanks
SHADOW
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*"
Any help for this statement would be great thanks
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?
View 1 Replies View RelatedI am typing in a long SQL string and seem to hit a character line limit in VBA...
How do I programmatically return to the next line and have to code continue to read the SQL String?
I need to add more fields and this is too long..
How Can I remove line breaks in a string.
View 6 Replies View RelatedI have a vba module call runme with this code
Option Compare Database
Sub makequery()
Dim db As DAO.Database
Dim qry As DAO.QueryDef
Dim rs As DAO.Recordset
Dim fld As DAO.Field
Dim XlApp As Excel.Application
Dim xlwb As Excel.Workbook
Dim xlws As Excel.worksheet
Dim xlrn As Excel.Range
Dim x As Integer
Set db = Access.CurrentDb
Set qry = db.QueryDefs("q1")
Set rs = qry.OpenRecordset
Set XlApp = New Excel.Application
XlApp.Visible = True
Set xlwb = XlApp.Workbooks.Add
Set xlws = xlwb.ActiveSheet
x = 1
For Each fld In rs.Fields
xlws.Cells(1, x).Value = fld.Name
x = x + 1
Next fld
Set xlrng = xlws.Cells(2, 1)
xlrng.CopyFromRecordset rs
xlws.Columns.AutoFit
rs.Close
qry.Close
qry.Close
End Sub
I can run it from vb eidit interface by hitting run.
How can I run it from command line?
I tried "msaccess.exe" "C:Documents and SettingsjzhuMy Documents est.mdb" /Excl /X "runme"
But it does not seems to work.
THX.
Jeff
In query:
I set the input parameters:
enter: [Please enter the value:]
After running, it shows "Please enter the value:"
This one I know
How can I make 2 lines showing
"Please enter the value:
eg. 0506:
enter: [Please enter the value: & vbcrlf & eg. 0506:]
It doesn't work.
Please let me know about it. Thanks.
I'm not sure if this is the right section for this question, but not sure where else to ask it.
We have a database and a query that is run that creates a table for a webpage.
The webpage (created in FrontPage) displays the info we need, but what we have 1 field that we would like to have display in a certain way. What we would like is, instead of having this 1 field string all the words out in a row.
For example:
word 1 word 2 word 3
Is there something we could either do in the query or within the webpage, that will force each word to a new line within it's table cell on the webpage
So would look something like this.
word1
word2
word3
it makes it easier to read on the webpage.
Hoping someone can help!:(
I have a table in access which holds interest rates details, and a query that displays the latest interest rate entered into the table (on the last line) by using the Last option in the Totals field.
The problem is that when I enter any new data into the table the query doesn't see this and continues to display the data which is now on the second to last line. The query for some reason just doesnt seem to notice the new last line.
Is there a solution to this so that it shows the new last line?
Thanks is advance!
Is there a way to reformat so that the line that is the next line to be entered is at the top instead of the bottom >> I hope that is clear
Also when i use the scroll bar at the bottom at the very end to the right it show grey in the background is there a way to limit it so only what is used is showing
Thx STeve