Modules & VBA :: How To Use Variables In Update Statement

Apr 10, 2014

What is the syntax for using variables in a VBA update statement? I have the following that I want to use to update a record field.

Code:
Dim thisTbl as String
If Answer = vbYes Then
If MedicationInvNo2 <> "" Then
thisTbl = "tblMyMedData"
Else
thisTbl = "tblMedData"

[code]....

I'm getting run-time error '3144' when the database tries to run the SQL. So I'm assuming my syntax is wrong (specifically in the WHERE clause)?

View Replies


ADVERTISEMENT

Modules & VBA :: Multiple Variables For Insert INTO SQL Statement

Sep 28, 2013

Look at the below SQL 'INSERT INTO' statement ? I'm trying to insert multiple variable values into an 'INSERT INTO' statement. I'm getting the below error message. The code is listed below. I started out with two (2) variables, but will have thirteen to insert into a table. Also, in the code below is the VBA statement to retrieve the variable data. I'm getting the data, but cannot insert the data into the table.

Private Sub Test2_Click()
Dim strSQL As String
Dim strSalesman As String
Dim strContentArea As String
DoCmd.SetWarnings False

[Code] ....

Error
Microsoft Visual Basic popup
Run-time error '3061'

Too few parameters. Expected 1.

View 5 Replies View Related

Access - SQL Statement That Uses Variables

Nov 13, 2014

I have a form that has multiple toggle buttons. I would like to be able to do the following when the toggle button is clicked:

Set the dateset the userlock both fields so that the information is not lost

Because there are multiple toggle buttons I would like to be able to use variables.

Here is a sample of what i have.

Private Sub CONTRACTTgl_Click()
CONTRACTDATE = Now()
CONTRACTEMP = CurrentUser
CONTRACT = "Collected"
Me.CONTRACTDATE.Locked = True

[Code] .....

View 8 Replies View Related

Modules & VBA :: Update Query End Of Statement Error

Aug 10, 2015

I'm trying to write an UPDATE line in VBA, but whenever I put the table name I get an "Expected End of Statement" error.This is line currently;

Code:
strSQL = UPDATE "MASTER PLANNER" SET [SET 2] = TRUE WHERE "[ID] = " & Me.PlannerID

It doesn't matter whether I change the table name to square brackets, quotes or nothing at all, it always gives me the expected end of statement error.

View 2 Replies View Related

Modules & VBA :: Can't Find Field In Update Statement

Aug 19, 2014

My update statement is shown below. What this is to do is change the PGTIN record in PP TBL to the GTIN that is in the UPC TBL where the UPC Code on the form is the same as the UPC Code in the UPC TBL. I am getting the error message "can't find field..." I have double and triple checked the field names but cannot find my error. Both PGTIN and UPC Code are text fields.

strSQL = "UPDATE [PP TBL] SET [PGTIN] = '" & [UPC TBL].[GTIN] & "' WHERE ([UPC TBL].[UPC Code] = '" & Me.UPC & "');"

View 6 Replies View Related

Modules & VBA :: Update And Amend Records In A Table - Error Trying To Execute SQL Statement

Jun 18, 2013

I'm trying to create a function to update and amend records in a table.

The update part works and updates existing records with new data but I'm getting an error with the insert part.

Run time error 3078
The Microsoft Office Access database engine cannot find the input table or query 'FALSE'. Make sure it exists and that its name is spelled correctly.

Nothing called 'FALSE' so not sure what that means?

Code:

sSQL = "INSERT INTO Pupil_tb (PupilID,Class,PupilName,etc ) " _
= "SELECT PupilImport_tb.PupilID, Class, PupilName, etc FROM PupilImport_tb " _
& "LEFT JOIN Pupil_tb " _
& "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
& "WHERE Pupil_tb.PupilID Is Null "

CurrentDb.Execute sSQL, dbFailOnError

View 5 Replies View Related

Queries :: Update Statement For A OUTER JOIN Select Statement

Feb 12, 2014

I have the following Select Statement:

SELECTTenant.ID, Tenant.[First Name], Tenant.[Last Name], Tenant.Address, Tenant.City, Tenant.State, Tenant.Zip, Tenant.[Home Phone], Tenant.[Cell Phone], Tenant.[Work Phone], Tenant.[Rented Unit],
Tenant.[Security Deposit], Tenant.[Move In], Tenant.[Move Out], Tenant.TenantID, Tenant.UnitID, Tenant.PropertyID, Tenant.OwnerID, Owner.Company, Owner.ID AS Expr1, Property.[Property Address],

[code]....

Now, I know that something in the UPDATE statement does not match my select statement.What should my Update Statement be, in order to update all the columns in the joined tables?

View 2 Replies View Related

Modules & VBA :: Dcount With Date Variables

Jul 11, 2015

Im a relative novice with access VBA, and I'm really struggling with using Dcount with date variable. All I want to do is count if a certain date appears in a table. Here is the extract from my code:

Dim WorkoutDate as Date
Dim datecount as integer

datecount = DCount("[WorkOut Date]", "tblworkoutlogs", "[workout date]= " & WorkoutDate)

Whatever dates are in tblworkoutlogs, datecount is still = 0...I've trawled the net and tried many variations of the code but no success!if I change all the date formats to strings in the code and the tables, it works so I know i'm looking in the correct place.

View 5 Replies View Related

Modules & VBA :: Passing Variables To A Sub Routine

Jan 13, 2014

I have a public sub routine which requires parameters to be passed to it when I call it from an access form. When I try to enter the code to call the sub I get a compile error. I've also tried calling it from another sub in the same module but get the same compile error - see below.

Code:
Sub EmailData(Datafile As String, To_mail As String, CC_mail As String, Subject_mail As String)
'code to use variables passed in
End Sub

[Code]....

View 4 Replies View Related

Modules & VBA :: Converting String To Variables?

Jan 30, 2015

I am trying to set up some template emails using text someone has entered in a form with a variable indicated with a key word in brackets aka. [ChangeID] or [ChangeDate]. The field on the form is formatted as Rich Text so I am getting http code. (No problems yet) In the form the template is required, I lookup the template required and I get the string. I replace the brackets with the following

Code:
strleftB = Chr(34) & " & me."
strRightB = " & " & Chr(34)
strTemplate = Replace(strTemplate, "[", strleftB)

I then get a string but in need to convert part of the string into variables, before I capture the correct output for my email

Code:
"<div>RFC Submission: <strong>" & me.ChangeID & ", </strong> " & me.Details & "</div>"

My question is: what is the best way to split the string into strings and variables

View 7 Replies View Related

Modules & VBA :: Possible To Initialize A Function Like Do For Variables?

Jul 15, 2015

Sometimes we need a one-line function to just get the database path or things like that we cannot do on a query or on a Macro Object.

Like:

Function GetDatabasePath() = currentproject.path

possible?

View 5 Replies View Related

Modules & VBA :: Assigning Index To Variables

Sep 11, 2014

How can I assign some type of index to the below variables (data representation) ? I'm using VBA to build a .RFT file for MS Word.

"Line # 1 data"
"Line #2 data"
"Line #3 data"

. . . .

"Line #7 data"
"Line #8 data"
"Line #9"

. . . .

"Line # 22 data"
"Line #23 data"
"Line #24 data"

View 14 Replies View Related

Modules & VBA :: How To Set Up Variables When Access Starts

Oct 9, 2013

I need to set some 'global' variables with default values when my Access 2007 database is loaded. Depending on the user etc these values may be modifed after Access starts but defaults need to be set.

I declared the variables as 'Public' in a Module, then put a function in the same Module (to set the default values) then tried to call the function from an "AutoExec" macro, so it's the first thing that runs when Access loads.The macro throws an error - it can't 'find' the function(?)

View 2 Replies View Related

Modules & VBA :: Calling Variables From Other Subs

Oct 29, 2014

I have been tasked to create a multi-keyword search form, however, my form isn't working right and only the first record of the table is opened.

Code:
Public Sub txtSearch_AfterUpdate()
Dim strWhere As String
Dim strWord As String
Dim varKeywords As Variant
Dim i As Integer
Dim IngLen As Long

[Code] .....

These are the codes that I am using for my search form. I have a feeling that I am not calling the variable from the after update portion the right way.

View 14 Replies View Related

Modules & VBA :: Auto-fill On Variables

Apr 9, 2015

I have a Change Management database with several fields, the key fields that I need to talk to work together are as follows:

[Patch] , [CVE] & [Patch Approved]

When opening a form I have I can sort it by the patch number which will then sort out all other CVE's that are not associated with that patch. What I would like to be able to do is approve one patch under the [Patch Approved] field and have it carried through the rest of the filtered CVE's that have the same [Patch] number/ field.

View 1 Replies View Related

Modules & VBA :: Passing Variables And Retrieving Results?

Jul 9, 2014

I wrote this module

Option Compare Database

Option Explicit

Public Function OdometerInput(varodometer As Variant) As Long
Dim varKilometres As Variant
varKilometres = varodometer * 1.609344
OdometerInput = CLng(varKilometres)
End Function

It works fine in the immediate window (although I haven't just fathomed what to do with null values and such) But my question which I am sure will be 'easy when you know' is how do I pass the variable to it from a text box on a form and retrieve the data in another text box on a form.

View 4 Replies View Related

Modules & VBA :: Referring To Forms Objects Using Variables

Sep 30, 2013

I have a simple date stamp that works great in a private sub within a form. (error handling removed for clarity)

Code:

Private Sub btnDateStamp_Click()
' UserInit is global variable
Me!Notes.SetFocus
Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & UserInit & _
" -" & vbCrLf & Me!Notes

[code]...

I am rewriting it as Module function that is Called from various forms, to save space. The function receives the parameters varFormName and varControlName. I wish to write the results of the function back to a memo field on the form.I am stumped at the get go by the need to refer to the Forms controls with a full reference instead of the Me command.

Code:

Function DateStamp(varFormName As String, varMemoName As String)
'varInitials, varFormName, varMemoName are global variables
Forms!varFormName.Controls!varMemoName.SetFocus ' Error here
'Me!Notes = Chr$(13) & Date & " - " & Time() & " - " & varInitials & _

[code]...

how to refer to the forms control's with their full reference, from within the Module's function, the rest will fall into place.

View 3 Replies View Related

Modules & VBA :: Applying Filter In Form With 2 Variables

Jun 6, 2013

I am fairly new to VBA and am trying to apply a filter within a form so that agent records can be filtered based on an agent's name (so that they can only see information that refers to them).

I also want a record to be removed when it has been completed (when this habbens a date completed field will populated) however I do want to see those completed records that refer to the agent that have been completed today.

Here is my code. The 2 filters work on their own howver when I put them together I get a mismatch error.

If Me.txtRole = "Agent" Then
DateCompletedFilter = "(DATECOMPLETED Is Null) Or (DATECOMPLETED = Date())"
AgentFilter = "CASEOWNER ='" & Me.txtName & "'"
DoCmd.ApplyFilter , AgentFilter And DateCompletedFilter
Exit Sub
End If

View 13 Replies View Related

Modules & VBA :: Import Spreadsheet With Form Variables

Jul 18, 2013

I'm looking at importing data into a table from a spreadsheet. The spreadsheet will only contain a single column of data, while the table in Access will have a few more, some of which will would be will provided from the form the user is using to import the data, and some at a later date.

Is there a way to do this? I've found the "DoCmd.TransferSpreadsheet acImport" command, but not sure this fulfills what I need, as I can't see a way to set variable data.

Here is a brief idea of what my Table would look like, where where the data to import would be obtained from

Fields:
PromoID, DiscountID, PromoCode, AddedBy, DateAdded, UsedBy, DateUsed

Spreadsheet
PromoCode

Form:
DiscountID, AddedBy (environ("username") query), Date ( DATE() )

Note, the PromoID is a autonumber.

View 2 Replies View Related

Modules & VBA :: Setting Filter On A Form With Variables

Aug 26, 2014

I am trying to set a filter on a form with VBA using variables and having no success at all. The code I tried last, which seemed to be "close, but no cigar", was:

Code:
Dim sFilterValue As String
Me.FilterOn = False
sBiller = Me.txtbiller.Value
sLastSource = Me.txtbiller.ControlSource
sLastSource = "[" & sLastSource & "]"
sFilterValue = sLastSource & "=" & sBiller
Me.Filter = sFilterValue
Me.FilterOn = True

sLastSource and sBiller are global variables. When I debug this code the sFilterValue is exactly what I would plug in manually and the Me.Filter shows up as "[Field Name] = Filter Value". But it crashes on Me.Filter = sFilterValue. I have tried all sorts of combinations but nothing I've tried works. What is the proper syntax for using variables as filters?

View 2 Replies View Related

Modules & VBA :: Calling A Function And Passing Variables

May 29, 2015

I have never tried passing variables while calling a function so I don't know what the heck I'm doing. I'll give a simplified example of what I'm trying to do. The second variable vRank is reporting properly but the first one vID gets "stuck" on whatever the first item in the listbox is.

Code:
Dim vrt As Variant
Dim upSQL As String
For vrt = 0 To Me.List1.ListCount - 1
If Me.List1.Selected(vrt) = True Then
Call ChangeUp(Me.List1.Column(0, vrt), Me.List1.Column(1, vrt))

[Code] ....

View 3 Replies View Related

Modules & VBA :: Initializing Date And Time Variables

Jul 1, 2014

I am writing a process for a hospital application in the UK

We have a concept of "Takes" which are periods to which patients coming into hospital are assigned to and thus the consultant (specialist) who looks after them.

They are fixed times between 0800-2000 (Day take) and 2000-0800 (+1) Night take.

I have written the following function to try and determine which take a patient should be assigned to based on the time now to power various queries to enable the end user to quickly see "today's day take" and "yesterday's take"

Testing it I seem to not be able to hit the right "Take times" in my output. I suspect it's a problem with the way I'm initiating my variables as Today 0800 and Today 2000

Code:
Function GetTakeStarttime()
Dim StartTime As Long
Dim EndTime As Long
Dim CurrentTime As Long
Dim YestStartTime As Long

[Code] .....

View 2 Replies View Related

Modules & VBA :: Variables As Field Names In Recordset

Mar 12, 2014

I am using Access 2013 under Windows 7. In my database I have a table, tblStock, with field names Module, Component_1, Component_2, etc. up to Component_50. I also have fields Qty_1, Qty_2, etc. up to Qty_50. These field names are not easily changed as they are constantly updated from another database. I want to create a table, tblTempBOM, using VBA by selecting a particular value of Module selected from a combo box on a form, from tblStock and creating a record with the Fields "Module", "Component" and "Qty" for each Component and Qty from 1 to 50. I am trying to use a From... Next... loop to cycle through the Component and Qty fields and store the data in the new table.

Dim strModule As String
Dim strSQL As String
Dim strQty As String
Dim strEye As String
Dim strTest As String

[code]....

This runs to the point where I try to set the value of rcd![StockCode], but the item rcdStock![strTest] has no value.Can I reference the value of the field in the rcdStock Recordset in some other way that would work?

View 5 Replies View Related

Modules & VBA :: Checking For Blank Textboxes (Variables)

Mar 2, 2015

I want to check to see if ALL textboxes are blank, then issue a message. My "Select Case" statement and "If" statement does not work. How can I accomplish this ? Below is what I have.

Blank = Null
Select Case Blank
Case strChartOfAccts1,
strChartOfAccts2, strChartOfAccts3, strChartOfAccts4, strChartOfAccts5 & _

[Code] .....

View 5 Replies View Related

Modules & VBA :: Displaying Content / Value Of A Couple Of Variables

Sep 24, 2013

Debugging my VBA code. I'm trying to track/display the content/value of a couple of variables to make sure they have the right value at a certain point.

What is the VBA code to say "display content of variable Var1" ? Where exactly will it display the value when instruction is executed?

I know I can just hover the mouse on a variable to see its content but some string varables like SELECT commands are too long and you cannot see the whole string.

View 2 Replies View Related

Modules & VBA :: Open Recordsets As Public Or Global Variables

Apr 22, 2015

Is there any way of opening recordsets as public or global in a form? E.g. if i do something like

Set rs = db.OpenRecordset("TblCustomers", dbOpenSnapshot, dbReadOnly)

the variable rs should be available in all the private sub i have on that form, and i should be able to access records without opening database/ record sets for individual subs.

View 6 Replies View Related







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