Insert form variable into table Results

I have a form that displays line items and their cost. At the bottom of the form I have a text field called text17 that uses a sum to total the value of all the line item costs with this : =Sum([Line Cost])

How would I pass that value into a database table?

Hi Again,

I really struggle with the syntax using SQL in VBA and need help yet again.

I simply wish to insert unbound form variables into an SQL Server table and have the following code:-

	MyName = Me.Cust_Name (this is text)
MyAdd = Me.Address (this is text)
MyNum = Me.Contact_Number (this is integer)
MyMail = Me.E_Mail (this is text)
StrSQL = "INSERT INTO dbo_tblCustomer" & _
          "(Cust_Name, Address, Contact_Number, E_Mail) VALUES ('"  & MyName & '" , "' & MyAdd & '"," & MyNum & ","' & MyMail
& '")"
DoCmd.RunSQL (StrSQL)

I am getting a syntax error and know it is something to do with the quotes and ampersands but am at a total loss.

Could anyone please help

Hi Guys,

I'm having a small problem. I am trying to insert the data for the report from a table, instead of having the text already on the report, as i want to allow the user to be able to edit it on another form and thus change the content of the report.

The only problem i'm having is when they reference variables, it is basically putting " & [variable] & " into the textbox and not the actual value of the variable.

You can see this from the following screenshot, notice the above textbox is the old one, which has the variables filled out, where as the bottom textbox is capturing the string from a table and is not displaying the values of the variables, but instead the call.

Any ideas of how i can get around this?

Thanks in advance.

Hello folks,

I have a an unbound popup form (popNewCustomer) that allows the input of data into a Customer Table (tblCustomers).

Once the form has been completed, I have a sub that puts the completed forms data into the table via an SQL Insert. All good.

The form then closes.

I'd like to automatically navigate to the record in question in the (always open) main form (frmMainDisplay) - but because I'm using SQL to insert the record, I don't know it's CustomerID. I do have a plethora of information to the tune of the variables I've just inserted - but no ID.... (Here's the SQL - the "var$$$$$" outputs the variable with single quotes so SQL can deal with it.

	trSQLstatement = "" ' to be sure it starts blank
        strSQLstatement = strSQLstatement & "INSERT INTO tblCustomers ( CompanyName, AddressL1, AddressL2, AddressL3, City,
PostalCode, MainTel, MainFax, InceptionDate, BoolClient) "
        strSQLstatement = strSQLstatement & "SELECT " & varCustomer & " AS CompanyName, "
        strSQLstatement = strSQLstatement & varAddrL1 & " AS AddressL1, "
        strSQLstatement = strSQLstatement & varAddrL2 & " AS AddressL2, "
        strSQLstatement = strSQLstatement & varAddrL3 & " AS AddressL3, "
        strSQLstatement = strSQLstatement & varCity & " AS City, "
        strSQLstatement = strSQLstatement & varPostcode & " AS PostalCode, "
        strSQLstatement = strSQLstatement & varTel & " AS MainTel, "
        strSQLstatement = strSQLstatement & varFax & " AS MainFax, "
        strSQLstatement = strSQLstatement & varInception & " AS InceptionDate, "
        strSQLstatement = strSQLstatement & varClient & " AS BoolClient"

So, in summary, I just need to either navigate to the latest created record or the record relating the one just made.

Can anyone help me please?

This particular code is part of a custom login system where users log into the database in order to access specific parts of the database. The database is held server side and the client wishes to have a user log (to see who is currently logged onto the system) I need to be able to store the details of a specific user into a table called "Current Users"

On the login form i have a combo box named frm_username which is bound to the ID of the users table in order to check against the password and username simultaneously. The row source of this box is
SELECT users.Username, FROM users ORDER BY;

This code (along with other coding) is appended to the on_click() property of the "Submit" button on the login form:
DoCmd.RunSQL ("INSERT INTO [Current Users] ([First name], [ID], [House], [Year], [Last name], [Username], [Account type]) SELECT [First name], [ID], [House], [Year], [Last name], [Username], [Account type] FROM [Users] WHERE Users.username= (" & Me.frm_Username.Value & ");")

For some reason when i run the above SQL code i am getting "Data type mismatch in criteria expression".

Any help would be gladly appreciated.

I am not new to Access but new to Modules and VBA. I have a bond table that houses the basic info and a bond schedule table that I append records to based on the bond table data. I have already developed VBA that inserts the records. IN my learning and development, I hardcoded some of the variables and parametertized others.

Now, I have a form bound to the bond table to capture the user input. I am struggling with how to identify the fields on the form to feed to the VBA procedure I developed. My work thus for looks like below.

This is form to capture user input (appartenly I don't have 10 posts so I can;t include the image or link to image to show you) Basically, the form has 10 fields need to feed as variables to VBA script to allow the calculation and insertion of records into the bond schedule table. I need help with VBA script in how to identify each of those ten fields.

Below is the code I have created that does insert records into bond schedule table. Once I know how to identify data on form I can adapt code.

Public Sub AddPayNo2(totalpayment As Integer, firstdate As Date, _
origprin As Currency, _
mmbbAFee As Double, depAFee As Double)
'declare variables
Dim vt_bondid As String
Dim vi_payno As Integer
Dim vd_duedate As Date
Dim vi_monthdue As Integer
Dim vc_principal As Currency
Dim vc_outstandprin As Currency
Dim vc_interest As Currency
Dim vc_mmbbA As Currency
Dim vc_depA As Currency
vd_duedate = firstdate
vc_outstandprin = origprin
'enter new payment record
For vi_payno = 1 To totalpayment
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
With rst
vt_bondid = "PJCTest2"
'vd_duedate = DateAdd("m", vi_payno * 6, firstdate)
vi_monthdue = DatePart("m", vd_duedate)
If vi_monthdue > 6 Then
vc_principal = (origprin / 20)
Else: vc_principal = 0
End If
vc_interest = Abs(IPmt(0.01, vi_payno, totalpayment, vc_outstandprin))
vc_outstandprin = vc_outstandprin - vc_principal
vc_mmbbA = (vc_principal + vc_interest) * mmbbAFee
vc_depA = (vc_principal + vc_interest) * depAFee

.Open "Peter", CurrentProject.Connection, adOpenDynamic, _
.Fields("BondID") = vt_bondid
.Fields("Payment#") = vi_payno
.Fields("DueDate") = vd_duedate
.Fields("MonthDue") = vi_monthdue
.Fields("Principal") = vc_principal
.Fields("OutStandingPrin") = vc_outstandprin
.Fields("Interest") = vc_interest
.Fields("MMBB_AdminFee") = vc_mmbbA
.Fields("DEP_AdminFee") = vc_depA
End With
vd_duedate = DateAdd("m", vi_payno * 6, firstdate)
Set rst = Nothing
Next vi_payno
End Sub

Trying to write day by day for a month's period from the counter in a loop as the day. When I run it, I get a pop up asking to enter the parameter cnt. How do I use that variable in the insert string?


Private Sub AppendRecs_Click()

Dim cnt As Integer
Dim sqlstrg As String
Dim check As Integer
Dim db As DAO.Database

Set db = CurrentDb
check = MsgBox("Do you wish to create the month?", vbYesNo, "Continue")

If check = vbYes Then
If DCount("Month", "test", "[Month]=" & Me.Fmonth & " And [Year]" = Me.Fyear) = 0 Then
cnt = 1
Do While cnt

Hi All,

I am hoping soemone can help me?

I have code which will allow me to access files and folders form a folder with various files and folders within it @ "C:CD Data", eventually I will change the path to read a CD but temporarily the information is on my hard disk. The code below sends it to message boxes based on what it has found within the folder;

	Private Sub CmdSearch_Click()

    Set fs = Application.FileSearch
    With fs
        .LookIn = "C:CD Data"
        .FileName = "*.*"
        .SearchSubFolders = True
        If .Execute(SortBy:=msoSortByFileName, _
                SortOrder:=msoSortOrderAscending) > 0 Then
            MsgBox "There were " & .FoundFiles.Count & _
                " file(s) found."
            For I = 1 To .FoundFiles.Count
                MsgBox .FoundFiles(I)
            Next I
            MsgBox "There were no files found."
        End If
    End With

End Sub

I have also found an SQL statement which allows me to add data to a table;

	INSERT INTO tblFilename (fldFieldname)
Values ('[Variable FoundFiles(I) from above]');

I am wondering if the two can be merged together in order to place the data from the files and folderpath's can be imported into the table tblFilename with one field fldFieldname? The table only has one field.

I have tried;

	Private Sub CmdSearch_Click()

    Dim dbs As Database
    Set dbs = OpenDatabase("C:CD Labeller - Microsoft Access 2003CDlabel.mdb")
    Set fs = Application.FileSearch
    With fs
        .LookIn = "C:CD Data"
        .FileName = "*.*"
        .SearchSubFolders = True
        If .Execute(SortBy:=msoSortByFileName, _
                SortOrder:=msoSortOrderAscending) > 0 Then
            MsgBox "There were " & .FoundFiles.Count & _
                " file(s) found."
            For I = 1 To .FoundFiles.Count
                MsgBox .FoundFiles(I)
                dbs.Execute " INSERT INTO tblFilename "_
                    & "(fldfieldname) Values "_
                    & "('.FoundFile(I)');"
            Next I
            MsgBox "There were no files found."
        End If
    End With

End Sub

With no success, syntax error on red above......

I got the idea from a microsoft example;

This example creates a new record in the Employees table.

	Sub InsertIntoX2()

    Dim dbs As Database

    ' Modify this line to include the path to Northwind
    ' on your computer.

    Set dbs = OpenDatabase("Northwind.mdb")

    ' Create a new record in the Employees table. The 
    ' first name is Harry, the last name is Washington, 
    ' and the job title is Trainee.

    dbs.Execute " INSERT INTO Employees " _
        & "(FirstName,LastName, Title) VALUES " _
        & "('Harry', 'Washington', 'Trainee');"
End Sub

But maybe this is better in the module rather than the CmdSearch button

If anybody can help, I would appreciate it.


Hi all,

I am very new to the world of VBA and I have a problem. I took over for a programer who was in the middle of developng a data entry form for our database. It created records in a tempory table and on exit and saving it ran an append query and added the new records to the live data. However when I run the program I get a syntax error in the following code:

	'Update Coordinate table
        DoCmd.RunSQL "INSERT INTO Coordinate ( WellID, LocationMethodID, DatumTypeID, LatDD, LatMM, LatSS, LonDD, LonMM,
LonSS, Township, NSCardinal, Range, EWCardinal, [Section], Quarter1, Quarter2, Quarter3, Quarter4, Quarters, Northing,
Easting, TownshipRange, LatDecimal, LonDecimal )" & _
        "SELECT " & varWellID & ", tblCoordinate.LocationMethodID, tblCoordinate.DatumTypeID, tblCoordinate.LatDD,
tblCoordinate.LatMM, tblCoordinate.LatSS, tblCoordinate.LonDD, tblCoordinate.LonMM, tblCoordinate.LonSS,
tblCoordinate.Township, tblCoordinate.NSCardinal, tblCoordinate.Range, tblCoordinate.EWCardinal, tblCoordinate.Section,
tblCoordinate.Quarter1, tblCoordinate.Quarter2, tblCoordinate.Quarter3, tblCoordinate.Quarter4, tblCoordinate.Quarters,
tblCoordinate.Northing, tblCoordinate.Easting, tblCoordinate.TownshipRange, tblCoordinate.LatDecimal,
tblCoordinate.LonDecimal " & _
        "FROM tblCoordinate"

I was wondering if it is a syntax error that I am missing or if it has something to do with the inclusion of a variable in the append or something more complicated?

The more complicated has to do with the key for the coordinate table, it is an autonumber that is generated when the other data is added, I am assuming it is still automated when these fields are inserted in to the table but perhaps it is not and that is the error?

Any help would be GREATLY APPRECIATED!!!

This is my first posting on a forum so I hope it makes sense. I would appreciate any help available
I run the SQL shown below from a form button using DoCmd.RunSql without any trouble. Table A is a yearly rota for team A. I also have table B for team B, table C for team C etc.

INSERT INTO [All Teams] ( Name, [Tot Hrs] )
SELECT Namelist.Name, Sum(A.[Tot Hrs]) AS [SumOfTot Hrs]
FROM (A INNER JOIN Namelist ON A.Team = Namelist.Team) INNER JOIN Calendar ON A.Date = Calendar.Date
WHERE (((Calendar.Week)=[forms]![selection]![week]))
GROUP BY Namelist.Name;

Can I use a variable (in place of A in the SQL) and a For – Next loop so that after the SQL has appended the required records from table A into table All Teams it then loops and appends the same records from table B then table C etc. If this is possible could somebody please show me how to do it.
Many Thanks

I have a questionnaire webform that send data to my access database. In my database I have a few tables: Users (FullName, age, date, medical record), table Questions (stores question number, question answer, iduser) and table results (stores predefined scores for each question/number and idquestions).

You can see from my code that I select Iduser and insert it into table questions. What I am trying to do for last several days is to select newly created idQuestion from table Questions and insert it into table results so we can calculate points for each questionnaire. I am new in ASP and would greatly appreciate if you could help me with this problem.
Here is my code and I just wish to add a peace to insert newly created idQustion to my table results:

I am trying to insert a record into a table using the following code:-

CurrentDb.Execute ("INSERT INTO Jobs (startTime, finishTime, hoursWorked, description, mechanicID, nonProductive) VALUES ('" & txtstartTime.Text & "', '" & txtfinishTime.Text & "', '" & txthoursWorked.Text & "','" & txtdescription.Text & "','" & cmbmechanicNames.Value & "', '" & cbnonProductive.Value & "') ;")

But I am getting an error message saying I cannot reference a property or method of a control unless it has the focus.

Now obviously I can't set the focus for each of the control on the form where I am trying to get the values from in the INSERT statement, so what am I doing wrong?

*Edit - and if I use variables to get the values from the form rather than try and get them directly from the controls, and then use the values in the INSERT string, then the record isn't inserted to the table.

Hello Everyone,

I have a query which insert data into table then i made a report with that table successfully now all i have to do is to make a form that execute the query on the some specific rules. then open the report.

Form has two fields.
Drop Down Box Named "Moths" and other is "Year" I extract both month and year and make a comlete date using dateserial function successfully
what i need is how call that query named "QRY_runone" and pass this "Para"[A variable having complete date] to the query to filter the record and then open the report named "RPT_current." both work is done under this sub routine. click event.

	Private Sub Command3_Click()
Dim Para As Date
Dim Mon As Integer
Select Case CStr(CMB_Month)
Case Is = "Jan"
    Mon = 1
Case Is = "Feb"
    Mon = 2
Case Is = "Mar"
    Mon = 3
Case Is = "April"
    Mon = 4
Case Is = "May"
    Mon = 5
Case Is = "Jun"
    Mon = 6
Case Is = "July"
    Mon = 7
Case Is = "Aug"
    Mon = 8
Case Is = "Sep"
    Mon = 9
Case Is = "Oct"
    Mon = 10
Case Is = "Nov"
    Mon = 11
Case Else:
    Mon = 12
End Select
Para = DateSerial(CInt(CMB_Year), Mon, 27)
MsgBox (Para)
End Sub

I have a database which holds records of publications in journals, newspapers or other media. A minority of these publications are of interest to everyone in the company. Most are of interest to smaller audiences, i.e. groups of one or four individuals. I would be surprised if the total number of users will ever be more than 30. The number of publications will be in the thousands.

the database is not very user friendly, it needs an additional feature. What I would like to achieve is to add an individual component to the database so that a person can identify records that are of special interest to him/her. This would save a lot of scrolling through records and make the publication of interest much more accessible and user friendly to each individual.

The database is split in front-end and back-end. In general, people work from the same PC all the time but there are exceptions. If possible I’d like to cater for the exceptions too, if too hard the exceptions will have to restrict activities in relation to database work to their own PC.

One way I thought this could be approached is to have a table with a field Username (based on login of the user), a field with a publicationID and a field to identify if the user has selected if that publication is ‘of interest to me’ (checkbox).

Straight forward enough? Or are there better ways to do this?

If this set-up is OK I still have a problem. This set-up requires inserting the username and the publication number into the table when the user checks the box ‘of interest to me’. This checkbox is located on a continuous form showing all records. I suspect the table tblSelectedRecords must have a double key (Username and PublicationID). Correct? If this is a viable set-up, could someone give me a leg-up what the code would look like in the after-update event of the checkbox?


Here's the problem (And believe me its driving me MAD)....probably really simple but I cant seem to suss it.

I have my own Logon form with a Combo Box txtUserName to pull up the username from an Employees table. This works fine.

I want to pull the username from the form above into a field in another table.... so that when the user activates a button (in a form) thier userid is inserted into an already existing record's field.
(in this case the table EMP and field QC)

I've tried to use an update query but I cant get it to pull the username form the form (I've left the form open)

I've also been trying to do this though global variables but haven't had much luck.... (my VB is non-existant)

I'm guessing that this can be done through a amend query with a lookup to the form called frmlogon, but I can't get it to pull the data and add into an existing record.

It may be of help to show what I have so far........

below are:

logon VB

Variables VB
Private Sub Form_Click()
Option Compare Database
Private intLogonAttempts As Integer

Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(Me.txtUserName) Or Me.txtUserName = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtpassword) Or Me.txtpassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Exit Sub
End If

'Check value of password in tblEmployees to see if this matches value chosen in combo box

If Me.txtpassword.Value = DLookup("Password", "Employees", "[ID]=" & Me.txtUserName.Value) Then
ID = Me.txtUserName.Value
GetUserName = Me![txtUserName]

'Close logon form and open Main Menu Form

' code to close form removed ****** DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "Main Menu"

MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database. Please contact your system administrator.", vbCritical, "Restricted Access!"
End If

End Sub

Option Compare Database
Dim PstrUserName As String

Function SetUser(UserName As String) As String
PstrUserName = UserName
End Function

Function GetUser() As String
GetUser = PstrUserName
End Function

Any help would be a huge help .....................


homemade vaporizers

Using the sql profiler gives me no clue.

This is the insert query from the profiler.

exec sp_executesql N'INSERT INTO "ENVIS_GSD".."wat_springflow" ("result_dt","site_id","result_va","remark_tx") VALUES (@P1,@P2,@P3,@P4,@P5,@P6)', N'@P1 datetime,@P2 int,@P3 float,@P4 nvarchar(4),@P5 varchar(3),@P6 datetime', 'Feb 10 2009 12:00:00:000AM', 3000723, 8.000000000000000e-001, N'test'

The two records that I get the proplem on are not included here, but somehow they have been updated. I traced the access code, but I could see no reference to them that they were used for the update in either the form or any modules called.

Somehow up to a few weeks ago those two columns were updated.

Tracing the code and using the immediate window the user_name variable stores the login name corectly.

Someone mentioned "error 28" any ideas?

Thanks in advance

What I am basically working on is a projects database to control R&D development through to full scale manufacture thus ensure all tasks are completed when and where necessary.

Currently the system is run via 100's of linked xls sheets all of which are updated individually and take an eon to go through and update. There are approx 50 - 100 projects running at once with over 300 steps & 20 different people responsible for tasks.

The project steps are set and stored in a table as are all the other variables so the data entry criteria is set. I have attached a file containing a picture of my relationships to help with the explanation below

I have 2 master tables, 1 which holds the tasks & responsibilities "LaunchProcessList_Tbl" & one which hold project specific info ie Customer, Launch date, Developer "ProductID_Tbl"

The info for the "ProductID_Tbl" is entered via a form with Combo Box data input.

Once a record is saved I would like to pull info from the "ProductID_Tbl" & the "LaunchProcessList_Tbl" into a cross over table, I am not quite sure how to do this.

I would like the crossover table to contain:

PK - ID - Autonumber
FK - Product ID - From "ProductID_Tbl"
FK - Gate ID - From "LaunchProcessList_Tbl" - approx 300 tasks import each Product ID

I will also add to this Due Date, Status and Comment

Unfortunatley I can't get my mind around how to get all the 300 tasks into my crossover table with reference to a single Product Id when updating my "ProductID_Tbl", ie

Product ID 1 - Task 1
Product ID 1 - Task 2
Product ID 1 - Task 3

I hope I have posted in a way that someone else will be able to understand!!!

I would be grateful for any help

Hope someone can help, I have a form with a listbox that uses a query as the recordsource. The query selects a few fields from a table, including word documents that are stored as OLE objects. The query is selecting these objects no prob.

Now, on the click of a button, I have a statement that loops through all of the selected items in the list, and inserts those values into another table. The statement works except the OLE object. In the code below, the OLE object is being inserted into EndorsementText and the value is Me.lstEndorsements.Column(3, varItem).
My main question is how do I handle this? I know that to insert text you need quotes around the variable, for dates you need #'s, but what about inserting OLE objects?

I hope this isn't too confusing, or stupid, but can someone please help me out?

DoCmd.RunSQL "INSERT INTO PolicyFormEndorsment(EndorsementID, EndorsementNumber, EndorsementName, QuoteID, EndorsementText) VALUES " & "(" & Me.lstEndorsements.Column(0, varItem) & "," & Chr(39) & Me.lstEndorsements.Column(1, varItem) & Chr(39) & "," & Chr(39) & Me.lstEndorsements.Column(2, varItem) & Chr(39) & "," & intQid & "," & Me.lstEndorsements.Column(3, varItem) & ")"

Thanks in advance,

Hi folks,
I have an Append Query (Insert Into...) that I use to insert values into a table from a form. But to have that tie into other tables I need to gather the Student_ID (primary key) that is autogenerated when the record is inserted. How do I do this? I want to store it in a public variable. I've done quite a bit of searching but nothing of any value has turned up. Please help.

What I have on button press:
DoCmd.OpenQuery "Register Camp Students"

After that I need something to gather that newly inserted Student_ID and store it for later use. Please help!


I have a questionnaire webform that send data to my access database. In my database I have a few tables: Users (FullName, age, date, medical record), table Questions (stores question number, question answer, iduser) and table results (stores predefined scores for each question/number and idquestions).

You can see from my code that I select Iduser and insert it into table questions. What I am trying to do for last several days is to select newly created idQuestion from table Questions and insert it into table results so we can calculate points for each questionnaire. I am new in ASP and would greatly appreciate if you could help me with this problem.
Here is my code:

Not finding an answer? Try a Google search.