Modules & VBA :: Insert Into Using Do While
Dec 4, 2013
I have two subforms: sf1 (Based on Table 1) and SF2 (Based on Table 2) in the first I have the fields and CODE QTY.
I need to insert into the second table, as many records as QTY. to enter a serial number for each unit of the item. For example:
TABLE 1
COD. QTY.
001 2
006 1
I must insert into Table 2
001
001
006
Dim num As Integer, contador As Integer
contador = 0
num = Forms![FacturasRec_NSF]![FacturasRec_Det_SF].Form![CANLFR].Value
Do While contador = num
Insertar
contador = contador + 1
[Code] ....
but I could not even.
View Replies
ADVERTISEMENT
Nov 18, 2013
Been trying to get this piece of VBA code to work but not sure why it isn't.
This SELECT statement works fine:
Code:
strSQL = "SELECT [" & strTmp & "].* FROM [" & strTmp & "] WHERE (([" & strTmp & _
"].[Work center]) Like '*' & [Forms]![frmImport]![txtCode] & '*');"
However when I try to use this statement to create a new table like this:
Code:
strSQL = "INSERT INTO [" & strNewTable & "] SELECT [" & strTmp & "].* FROM [" & strTmp & "] WHERE (([" & strTmp & _
"].[Work center]) Like '*' & [Forms]![frmImport]![txtCode] & '*');"
db.Execute strSQL
I get the "Too few parameters. Expected 1." error.
Why would the syntax for the WHERE criteria not work when going from a SELECT to an INSERT INTO using the same SELECT statement that works independently as a row source?
View 5 Replies
View Related
Oct 13, 2013
I want wage rate should be inserted automatically instead of manually in attendance sheet. In labourwage form, wage rate will be fixed for labour type with wagedate as and when required. Once I add attendate in attendform, it should look-up the table labourwage and auto insert wage rate entry in to wagerate column for the respective month ie from the given wage date up to next given wage date of labourwage. I have tried much, but not succeeded and presently entering manually which is time wasting. This should be possible with writing some code with VB.
View 14 Replies
View Related
Dec 9, 2014
I am reading through a table looking for duplicate values in the FullName text field. I want to store in a new table the duplicate records I find, storing just the MemberNumber and the FullName. When the VBA code runs and finds duplicates, the SQL statement to insert a new record into the Duplicates table asks for the value of LastMemberNumber and LastFullName when it already has the values and has displayed them in the message boxes! What am I doing wrong?
The code is:
Private Sub Command0_Click()
Dim rs As DAO.Recordset
Dim dbs As Database
Dim LastMemberNumber As Integer
Dim LastFullName As String
[code]....
View 7 Replies
View Related
Aug 8, 2013
after I insert a new record using INSERT INTO and then use DMax() (in a private sub) to look up the new record. About half the time DMax() pulls the new record based on the primary key field (AutoNumber) just fine. However, half the time it pulls the max record prior to the new record being inserted. I.e. record 1001 was added and DMax() pulls record 1000. I'm assuming that my issue has something to do with the timing of when the record is writen/saved in the table. Is there a simple method of refreshing the table that I can use prior to using DMax()?
View 2 Replies
View Related
Sep 6, 2013
How to Get latest id and inserted to another table like this image below
sdrv.ms/18HQ
See..i want in dbo_maid get id and insert to dbo_contract in maid_id
View 3 Replies
View Related
Jun 7, 2013
I'm now trying to speed up data entry within my database and have hit a brick wall with one part.Basically, this is within a form (for 'clauses') of which there is a one-to-one relationship with 'the Applicability' table (a series of fields with 'Yes/No' values for each and a related key field) - i.e. for each clause there are a series of circumstances when it will apply. I've set this up to create a record when one doesn't already exist using the default values (i.e. all applicable).
The method to speed up data entry is to have a pop-up form with unbound fields to list common Clause fields - including 'Applicability' as a subform. When entering a series of Clauses you tend to find they have the same 'Applicability' as their neighbour, so I would like to create a corresponding record in the Applicability table with the values set on the 'ClauseQuickAdd' form (Technically the 'ApplicabilityQuickAdd' sub form).I've edited an existing code within my database to provide two global variables (strAppFieldList and strSubAppFieldList) to put into the following statement:
Code:
DoCmd.RunSQL "INSERT INTO Applicability ( AppRelClause, "" & strAppFieldList & "") VALUES ("" & Me.Clause_ID & ", " & strSubAppFieldList & "")"
Bringing up Error 2498 "An expression you entered is the wrong data type for one of the arguments". So I presume that I haven't quite got the format right for transferring yes/no values (strSubAppFieldList). See below for an extract of strSubAppFieldList:
Code:
Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Manufacturer], Forms('ClauseQuickAdd')![ApplicabilityQuickAdd]![Supplier], ...
View 6 Replies
View Related
Jul 10, 2013
I need my INSERT statement to DLookup tbl_module_repairs and insert the information from a field based on a WHERE condition of primary key matching on the form and table. Here is what I have but it will not work on the last value:
Code:
Dim lookModType1 As Variant
lookModType1 = DLookup("module_type", "tbl_module_repairs", "prikey = " & Me.txt_prikey1.Value & "")
SQLtext4 = "INSERT INTO TBL_RF_TECH_LOG ([TechID], ActionStatus, Barcode, EquipmentDescription) Values (Left([txt_techid_rework_in], 2),'In Rework', '" & Me.txt_bc1 & "', " & lookModType1 & ");"
DoCmd.RunSQL SQLtext4
Note: My DLookup works fine by itself for other uses.
View 5 Replies
View Related
Mar 4, 2014
I have an append query that contains an IIF statement. I want to code that into a VBA function. The SQL view of the query looks like this:
Code:
INSERT INTO tmpAvailInv ( NUID, Inv_Name, F_Name, M_Name, L_Name, Role )
SELECT tblPeople.NUID, tblPeople.[F_name] & IIf(IsNull([M_Name])," "," " & [M_Name] & " ") & [L_Name] AS Inv_Name, tblPeople.F_Name, tblPeople.M_Name, tblPeople.L_Name, tblPeople.Role
FROM tblPeople
WHERE (((tblPeople.Role)="Investigator") AND ((tblPeople.Archive)=False));
What I wrote for the VBA code is this:
Code:
Dim strSQL As String
Dim db As Database
Set db = CurrentDb
[code]....
Where it chokes is on the IIF statement with the double-quotes in it. I've tried several combinations with single quotes and double double-quotes. I'm just not getting it.
View 4 Replies
View Related
Jun 5, 2013
I currently have a bound form that adds a new student, however, I've decided I don't like bound forms as weird stuff can happen (like when the user exits in another way than I anticipated) so I'm making an unbound form which will add the student when a button is clicked. I already have this working on some other places so this should be no problem anymore.
However in my database a lot of student information is optional and only a first and last name are required (and ofcourse an autogenerated PK). So my question is how do I deal with these fields that might or might not be empty?
I could make a fairly big If ... ElseIf... construction where I slowly fill a String with all the optional fields but I was wondering if maybe there are some better ways to approach this?
View 12 Replies
View Related
Oct 4, 2014
I use the botton code for save image in SQL
Dim fDialog As Object
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Dim varFile As Variant
Set Cn = New ADODB.Connection
Cn.Open "Provider=SQLOLEDB;data Source=.;" & _
[Code] ....
But I have a problem when my database more than 2000 record. I think my problem in
rs.Open "Select * from Table_2", Cn, adOpenKeyset, adLockOptimistic
because select all record in Table_2
I decided to use bottom code
strSQL = "INSERT INTO Table_2(image_v,sabt,filetype_v,imageid) Values (mstream.Read,'" & [kodimage] & "','" & [file_type] & "','" & Me.Text8 & "');"
Cnxn.Execute strSQL
But I get an error with mstream.Read!!!!
View 3 Replies
View Related
Nov 11, 2014
I have a form that is calling data from a table. Then the user can determine if they approve the question or not. So what I am trying to do is take one combo box that has yes or no and write it to the database. I thought I had it correct, but appears I do not as it is not writing yes or no to the table. The field initially has no value and that is my query for calling the data for the user to select from. Here is the code that I have so far
<code>
'add approval to yes
CurrentDb.Execute "INSERT INTO travelerQA(approval)" & _
" VALUES('" & Me.cmbapproveda & "')"
</code>
View 5 Replies
View Related
Jun 11, 2013
Why I get a runtime 3134 error on this piece of code.
They are all text values
Code:
Dim strUserName As String
strUserName = Forms!FrmPrimaryData.FrmPrimaryDataInstallsSubFrm.Form.txtMacAddress
Dim strIDValue As String
strIDValue = Nz(DMax("[ID]", "radreply"), 0) + 1
Dim strAttribute As String
[Code] ....
View 6 Replies
View Related
May 31, 2015
I need to insert(append) first and last line in xml file...
So I have xml file and I need to insert one line to the top and at the of the text... Xml file contains Cyrillic characters and method to recreate xml file is going wrong with characters conversions... The last line a can append easy but the first line is a problem...
View 10 Replies
View Related
Jan 22, 2014
I am having a problem with below and getting a run-time error 3134
Code:
LastOrderNumber = DMax("Order", "Model_types")
NewOrderNumber = CLng(LastOrderNumber + 1)
CurrentDb.Execute "INSERT INTO Model_types (Order) " _
& "VALUES (" & NewOrderNumber & ")"
The field 'Order' in Model_types is a Long Integer.
View 4 Replies
View Related
Jun 17, 2013
I'm trying to search through a table with serial card IDs, Order Numbers, and Ship Dates. I'm search through the Serial Card ID column and making my code work so that if the serial card ID exists and its ship date is between 9/30/2001 and 10/1/2011, then retrieve the associated order number and put in array. All of this works perfectly except for one tiny part : it's not returning a null for either unfound serial Card ID or serial card Ids that exist but their ship dates do not qualify. I would like to return a null value for these fields into the array as well as keep the found values in the array. The whole point of all of this is so I could count the number of rows for all the found order numbers and return a total value with that number.
View 14 Replies
View Related
May 29, 2014
I have an insert query that has been working just fine. I added three new columns (Paid, Balance, Invoiced) to both tables that I am using in my query. The query will insert the new records that don't exist in the one table just fine but it is not populating the new columns.
' Append records from aud_ck_reg (local table) to dbo_aud_ck_reg (table on server)
DoCmd.RunSQL "INSERT INTO dbo_aud_ck_reg ( audType, audDate, audUser, transid, customer_number_parent, transdate, create_1131, check_number, check_date, PW_voucher_date, " & _
"voucher_date, voucher_number, customer_number, credit, debit, fiscal_yr, month_number, calendar_month, calendar_year, calculated_cost, notes, CIT_number, Quarter, Hyperlink, Paid, Balance, Invoiced)" & _
"SELECT aud_ck_reg.audType, aud_ck_reg.audDate, aud_ck_reg.audUser, aud_ck_reg.transid,
[Code] .....
View 4 Replies
View Related
Mar 8, 2015
I am trying to formulate some code to run an SQL statement but it is not working.
The statement runs from a button on the sub form subOriginForm. Its intention is to collect the values from cboField1 and txtfield2 located on that form and insert them into tblTargetDB - but the whole thing isn't working.
Code:
Private Sub cmdMyButton_Click()
'DoCmd.SetWarnings False
INSERT INTO tblTargetDB ( field1, field2)
VALUES (forms!subOriginForm.cboField1, forms!subOriginform.txtfield2);
DoCmd.SetWarnings True
End Sub
View 4 Replies
View Related
Jan 12, 2015
I have the below SQL statement... In table2 there is another field called timestamp1... Is it possible to have the timestamp1 included in the below statement so that I will have a record of time the moment the records were inserted in table2? There is no timestamp1 field in table1.
Code:
strSQL = "Insert Into Table2(Business_Unit, Account) Select Business_Unit, Account From Table1"
View 1 Replies
View Related
Oct 24, 2013
I am using "INSERT INTO tablename(a1,a2)" function but i have to many "a" objecjts. How to carry over to a new line without having error?
View 10 Replies
View Related
Jun 5, 2015
How can I insert an 'ID' field into an existing table at first field as primary key using AutoNumber? The table will then be populated.
View 9 Replies
View Related
Jun 4, 2013
I`m currently having the problem to export data from an SQL server into a table. I managed to open a recordset but I`m incapable of adding the recordset to an existing table. I found similar threads but I am still not able to generate functioning code.
Code:
Function fDAOServerRecordset()
Dim db As DAO.Database
Dim dblcl As DAO.Database
Dim rssql As DAO.Recordset
[code]....
View 2 Replies
View Related
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
Jul 17, 2015
On a form I have a textbox with a data type of Date/Time. If the user has the cursor in that text box and types the letter "t", I want to automatically insert today's date. I also want them to have the option of manually typing in a date, i.e. 05/12/2001 or use the Date Picker "calendar" item. This is using Access 2010.
View 3 Replies
View Related
Oct 10, 2013
Access 2010. Can vba code be written to delete and insert data from a table in one routine.
Something like Delete * Insert into select from where order by....
I tried it with a query but have to write two.
View 1 Replies
View Related
Mar 11, 2015
I am working on a timesheet application which allows users to insert multiple timesheet entries in a grid style format. the first row is visible and to add another row users click on a command button which makes the next row of fields available and ready to fill in and so on and so forth. I need the fields to be unbound and then when the user clicks on a save button for example then it inserts those values into the relevant table. Table name is tbltimesheet, field names are id, companyname, project, activitydate, activityhours, activitynotes, username, userid
View 2 Replies
View Related
