Nearly There Just One Line Of Code Needed...

Mar 12, 2008

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

View Replies


ADVERTISEMENT

New Code Line

Dec 19, 2006

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.

View 14 Replies View Related

Wordwrap For A Line Of Code...?

Feb 12, 2008

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.

View 2 Replies View Related

Commenting SQL Code In-Line

Apr 7, 2008

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.

View 3 Replies View Related

Can I Simpifly This Line Of Code?

Dec 13, 2005

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

View 3 Replies View Related

Jumping To The Next Line In VB Code

Jul 7, 2006

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

View 7 Replies View Related

One Line Code Can Someone Explain Please?

Sep 5, 2006

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

View 1 Replies View Related

General :: Continue Long List Of Code To Next Line

Sep 12, 2013

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] ....

View 2 Replies View Related

General :: Code To Remove Line Breaks In Table

Jun 20, 2014

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 Related

Code Help Needed On Message Box Please

Apr 30, 2007

Hi All, Is it possible to have just one instance of this message box fire when one or all or any number of the flags are triggered. At the moment if 2 flags are set then 2 messgage boxes appear,if 3 then 3 messages etc. My code at the moment is below to trigger the message box, hope I make my self clear;can it be tidied up?





private Sub Form_Current()
If Me.Flag1 = "Patient Alert. HIGH RISK. Med Hist" Then
MsgBox "**WARNING PATIENT ALERTS DETECTED**Read all patient alerts before treatment"
End If
If Me.Flag2 = "Patient Alert. ALLERGY. Med Cond" Then
MsgBox "**WARNING PATIENT ALERTS DETECTED**Read all patient alerts before treatment"
End If
If Me.Flag3 = "Patient Alert. Cat Score. LOW. Caution See Notes" Then
MsgBox "**WARNING PATIENT ALERTS DETECTED**Read all patient alerts before treatment"
End If
If Me.Flag4 = "Patient Alert. PATHOLOGY. Int/Ext Exam" Then
MsgBox "**WARNING PATIENT ALERTS DETECTED**Read all patient alerts before treatment"
End If
End Sub

View 9 Replies View Related

Help On Message Box Code Needed

May 9, 2007

Hi All,:confused: need some code help again, I dont seem to be able to get the hang of message boxes!! The code below takes the path to a mdb file which is stored in a text box on my form, it then deletes an existing table -Expire-and replaces it with a new table also called Expire from the database located at the file path in the text box.

Private Sub Command6_Click()
Dim tmpFilePath As String
tmpFilePath = Me!Text1

DoCmd.DeleteObject acTable, "Expire"
DoCmd.TransferDatabase acImport, "Microsoft Access", tmpFilePath, acTable, "Expire", "Expire", False
DoCmd.Close

End Sub


What I would like to happen is; when the process has finished a message box pops up and informs the user that:

"the update has been successfull, your DentureBase V2 licence key has been updated"

the form then closes. If it is at all possible I would then like the db to close and restart (but this is not essential).

Once again I appeal to your generousity, many thanks:D

View 3 Replies View Related

Desperately Needed Code

Dec 28, 2004

Hi,
I have a database that has a combined user last name, user first name and user middle initial all in one field called name.

Example:

NAME
DOE JOHN I.
SMITH JOHN R.

I would like to get a code that will look at the field and separate them in three different fields, such as :

LNAME
DOE
SMITH

FNAME
JOHN
JOHN

MI
I.
R.


I would really appreciate any help.

Sincerely,
Monti

View 2 Replies View Related

URGENT HELP Needed For VB Code

Feb 10, 2005

Hello all
I am having a problem with my vb code. I am realatively new to vb and what I am trying to do is search through a table according to what is selected on my form and delete the values that equal the value selected int the table. What I have so far is this

Private Sub cmdDel_Click()
Dim rsSource As DAO.Recordset
Dim rsDestination As DAO.Recordset
Dim theBug As String
Dim SelItm As Control
Set SelItm = Me.Users
If Me.Schedule.ItemsSelected.Count = 0 Then
Beep
Exit Sub
End If
Set rsDestination = _
CurrentDb.OpenRecordset("User_Course_Room", _
dbOpenDynaset)

For Each Itm In Me.Schedule.ItemsSelected
rsDestination.FindFirst "User_ID = " & Me.Users & "" And "Course_Room_ID = " & Me.Schedule.ItemData(Itm) & "" ### THIS LINE GIVES ME A ERROR
'rsDestination!Course_Room_ID = Me.Schedule.ItemData(Itm)
'rsDestination.FindFirst "Course_Room_ID = " & Me.Schedule.ItemData(Itm) & ""
rsDestination.Edit
rsDestination.Delete

Next Itm

Me.Sessions.Requery
Me.Schedule.Requery

End Sub

In the line with the bold writing next to it I get an error. What I am trying to do is search for the record with the user id selected and the course room id seleceted and delete that record but I get an error. Any help would be highly appreciated.

View 2 Replies View Related

Modules & VBA :: Replace Line Of Code During Split Form Front End Deployment?

Aug 11, 2015

I 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.

View 6 Replies View Related

Queries :: Remove GROUP BY Line And Stick Semicolon At End Of Previous Line

Jun 25, 2013

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.

View 12 Replies View Related

Forms :: How To Get Values From Line Items Form Inserted Into QUOTE LINE

Jun 5, 2014

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

View 6 Replies View Related

Modules & VBA :: Read CSV Transaction File Line By Line And Add Correct Transactions To Access Table

Nov 29, 2014

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]....

View 8 Replies View Related

Modules & VBA :: How To Import Text File Line By Line

Nov 18, 2014

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.

View 14 Replies View Related

Forms :: No Line Only Marker In Line Graph

Oct 18, 2014

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?

View 1 Replies View Related

Please Review This Code, (simple Code) New With Codes

Feb 16, 2006

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

View 14 Replies View Related

Using Code To Unprotect And Protect Viewing Code

Jan 14, 2007

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

View 6 Replies View Related

SQL Line (Not Like)

Jun 20, 2005

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

View 4 Replies View Related

End Of The Line

Jun 29, 2005

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 Related

Next Line VBA

Mar 18, 2008

I 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..

View 4 Replies View Related

Line Break

Oct 17, 2005

How Can I remove line breaks in a string.

View 6 Replies View Related

How To Run It From Command Line?

Jul 20, 2007

I 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

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved