Hi, I need help with inputting multiple values in a table
I am using below statement in asp file and it works fine, when I input one value of name in form , as it takes that value and just input into the table. If it already exists then it just displays error message that this name value already exist in table as name field has no duplicate checked. I need to input multiple records now instead of just one.
The current statement I am using is
<%
Name = replace(trim(Request("Name")),"'","''")
SQL = "Insert Into table(tableName) Values('" & Name & "')"
objCon.Execute SQL
%>
But now I want to send multiple values from the text box instead of just one value, so I need a loop here. The values in the text box are separated with enter.
Please let me know how do I run this query in loop that it will input one name first and then input the second value and then third so forth. (input multiple records instead of just one).
Can you please give me the SQL statement for ACCESS or asp querry.
How can i retrive Bulk Data - from Different database into this database table. At the moment i loop through all the records which is not good idea...i want it to be real quick without linking the table...
I heard something like Insert into statement can work but have never used one....
I've a table containing ebay sales information, let's simplify it...
Code: buyer email product quantity price total Receipt Number A Smith a.smith@acme.com gloves 1 $10.00 $10.00 1 P Brown p.brown@jones.com hat 2 $15.00 $30.00 2
After a lot of faffing about (& learning about sub reports), I've now got access producing a sales receipt from the data....it's in the form of a report (the report generated is one long continuous report, with each page in the report relating to a sales receipt number)
What I want to do now is email each page of the the report (like I say, each page is essentially an individual sales receipt) to the associated email address in bulk.
Therefore there may be 20 sales receipts in a long continuous report, how would I break them out one by one to a pdf file & send it to the correct email address which is contained against the sales receipt number in the same table?!!!
I'd like to add 600+ records at a time to a table (or at least a lot) and do this 1200 or so times. Yes, this is a very big table, its okay and the size is not an issue.
The key here is that speed is very important. I DO NOT want to AddNew and Update a recordset 700k times (one for each new record). I want to add 600 records or so 1200 or so times.
I am open to any way of doing this in VBA and/or SQL in MS Access; however, I am thinking of building an array that has 600 rows and 16 columns, then somehow add that to the table all at once. I will size the array down if it causes problems. That isn't the issue. The issue is how to add a 2d array to a table in bulk (all at once).
Code: Sub test() Dim dbLocal As DAO.Database Set dbLocal = CurrentDb() Dim sql As String 'build my array Dim x(0 To 1, 0 To 1) As Integer
I have 12,000 cvs that i need to get into An access database so i can start to extract email info etc. They are stored in one folder All Cvs under each of their names edc.txt (i have converted them to .txt) i want to create one table with two fields name (taken from the cv filename and contents (taken fromthe contents of the .txt file). I am using Access 2007.
I want to make a database of diseases (need to learn them for school and would like a serchable database on my smart phone for future reference). Unfortunately spent a lot of time making hundreds of pages of word tables before i realized a database would be better. See attached image or pdf. Is there a way to import the tables to Access?
I need to preserve the hierarchic info in the nested bullet point lists. E.g. under treatment i might have a point called Acute treatment, with sub-levels, Step 1, Step 2, etc. with their own sub-levels. I need to maintain this relational hierarchic info.
I want some VBA code that can loop through the table above one by one returning the value. The table data will change so i would like the code to handle change as well.
I want the returning value to be returned has a string i.e. TblValue = <table data value>
Because then i want to use the value to be put in this sql query- DoCmd.RunSQL "INSERT INTO TEST_DOC SELECT * FROM " & [color:red] TblValue [/color]
I have a table named tblItems with a field "Qty".I have another table, tblItemsSold, with fields "InvNo" for invoices number and "QtySold".Both have UPC (barcode) fields.What I need to do is when the "Complete" button is clicked: All the Items (by UPC) with the same invoice number (InvNo) need to subtract the "QtySold" in tblItemsSold from "Qty" in tblItems.I would like to do this through VBA so I can just plug it into the existing code I have.
Please could someone enlighten me on the following scenario I have.
I would like to get from a button click the information from Sheet_subID (cat_parameter) copied into the Main sheet Description field. I think this maybe would be done with a loop? Taking one from in tunr from Sheet_subID and then putting it into the description filed until al cat_parameters have been copied.
This information is not beng duplicated as such because the text that lands in the description field will be modified slightly by the user. The information contained within the Sheet_subID are for presets for in the case below Compensator Setting 3. Compensator 1 would have different presets.
Could someone check the following code. i've set up a form for creating regular bookings, and have a field, for which you type in the frequency in days you want the bookings for. (ie, 7 days for every week on that day, 14 for every two weeks etc...) and it seems to work, however it just alters the one record, instead of creating entirely new ones. Could someone please help:
Private Sub cmdCreate_Click() Dim date2 As Date Dim period As String Dim cost2 As Currency Dim frequency2 As Integer
Do While year = actual_year
date2 = Date_For period = Time_Period cost2 = Cost frequency2 = Frequency
Date_For = date2 + frequency2 Time_Period = period Cost = cost2 Frequency = frequency2
DoCmd.Close acTable, "tblRegularBookings" Loop
End Sub
Also, one of the other problems i'm trying to solve is for those who want to create a regular booking but on say every third monday of the month for example.
I am trying to loop through a table and count the duplicate records only once. Please see the attached file.
1) In the test table, I want to loop through all pt_accts.
2) In the countable column, if an acct repeats, I essentially just want to put "1" in one of the records. For the non-repeating accts, putting in a 1 is sufficient. Basically, I just don't want to count the duplicate ones multiple times.
In the example, acct "aaa" repeats, but there is just one "1" in the countable column. "eee" repeats as well, but there is just one "1" and the rest is 0.
though my actual application contains 6 tables, I will give an example with 2 to simplify my question:
Say I have a simple DB with a Company table and an Employee table....the only attributes are CompanyID (autonumber/PK) and CompanyName for the Company table and EmployeeID (autonumber/PK) and EmployeeName for the Employee table.
Now I have an excel spreadsheet with Company Names and Employee Names on a worksheet. How can I import these to both tables and keep the relationship of who works where? Keep in mind that I have 6 tables in my actual DB.
I've set a database which has a table in which there are 2 fields "Account" and "Total Accounts". I want to have the amount of total summation of accounts in "Total Accounts" field of each record, which is the result of summation of "Account" values in all previous records till the current one. In order to do this purpose, I copied the value of "Amount" field of each record into "Total Accounts" field of the same record, at first. Then, I tried to add the amount of "Total Accounts" field of every record with just the amount of "Total Accounts" of previous one to earn the actual total amount of that record. I found that I need a VBA loop to do this query for all records (except first record) and so I code it as below, but it has the Run-time error '424' : Object required and it seems that I am in a mistake in definition of strSQL variable:
Code: Private Sub doDataSegm_Click() Dim dbs As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Set dbs = CurrentDb() Set rs = dbs.OpenRecordset("Table1", dbOpenTable)
I am trying to export a table in access based on a unique field called Group_Name. Say my table has 100 records. 10 of those records belong to Group1, 10 belong to Group 2, and so on. What I want to do is export those groups individually to an excel file and have that file named somting like Group1_Premium Detail Report.
Here is the Code I have so far. I have a feeling I am close. When I run the code it does not seem to like strrsql2
Dim strsql As String Dim strsql2 As String Dim strfilename As String Dim strpath As String strpath = "C:UsersDesktopHome"
I have a table with a field that contains IDs, e.g.
123 456 789
I would like to generate a single string from this table, seperated by commas, e.g. 123, 456, 789 and output to a field in an existing table. This will then be used in a SQL statement.I am new to VBA and don't reallly know where to start/
I have an MS Access 2010 db that has a main form called switchboard. This has 4 command buttons that open diffrent forms. Also on the main switchboard form i have an unbound textbox called TxtUserName that captures the users environ"username" when the switchboard form is opened.
I have a table called "tblAccessUsers" that i manually enter who i want to use my db. This table will have up to 50 names added to it. Their is only one field name in this table and it is "User Login".
When the user hits any of the commandbuttons on the main switchboard form i need some code that will look at the value in TxtUserName and loop through tblAccessUsers for an exact match. If it finds a match then it will carry out the open form command or if not prompt the user with a message box.
My knowledge of Access and especially VB is quite limited. I managed to create this using a DLookup but that only returns the first record in the table. The logic works but it will not look past the first record.
I have a VBA routine that runs some queries and then creates a report as a PDF document using my virtual PDF printer. At this point it brings up the Save As dialog and I give the file a name and select a folder to store it in.
But I run this report 30 times for 30 different parameters. The parameters are obtained from a table which contains values such as Cancer, Diabetes, Stroke
-I always name the PDF file according to this value. -I always choose the same folder. -I always overwrite the reports I produced last month (copies have been sent elsewhere by then).
how can I get VBA to cycle through this code 30 times, each time selecting the next value from my table and creating/overwriting those files without the need for me to tell it the filename and path?
Code: Private Sub lstSpecialties_DblClick(Cancel As Integer)
At the moment I am running this code by double-clicking an entry in the Specialty listbox, 'but I would prefer the whole thing to run multiple times for as many Specialties as exist in the table '(currently 30 but could be more in time)
'Warnings off DoCmd.SetWarnings False 'Make the tables needed for the cumulative queries DoCmd.OpenQuery "Spec 002 Monthly recruits - part 2 - make table" Each of these queries DoCmd.OpenQuery "Spec 005 Monthly recruits - part 2 - make table" uses the Specialty selected above DoCmd.OpenQuery "Spec 022 ABF previous year - part 2 - make table" as a parameter to get DoCmd.OpenQuery "Spec 025 ABF current year - part 2 - make table" the right data for the report
I have 192 databases that I need to redact certain information in certain columns. Generally, I'm looking for field names like name, first name, last name, address, address2, shipping address, mailing address, and phone numbers. Not sure how to get this information without going into every database and every local table inside the databases. Is there a way to get this information programmatically and just access the databases and table that I need to redact info in?
Im trying to create a loop that searches through a table and increments a variable every time it finds a record with a specific field set to a specific value. Below is the code but its not working. I then use the variable for another field.This is used in a form btw.
Dim counter As Integer counter = 0 Set rst = CurrentDb.OpenRecordset("TableName") Do Until rst.EOF If rst(10) = "True" Then counter = counter + 1 End If
I have a data base of ip addresses. I need to come up with a way to bulk assign Host names to the available ip addresses. The only data that is the same between the two tables is a Farm Name which is the same for each record. My first query creates a temp table that lists ip addresses by farm name that do not have a host name. That is my data pool. Then I import from Excel a list of host names (with farm name) that need ip addresses. What I am not sure how to do is "Combine" the tables. Putting a Host Name with an IP address.
Hello im new to this forum and I just recently became involved with access so please bear with me and my many questions. I work for a mining company in Canada and we have training events. These records must be kept showing who attended and what was covered.
I have used the event template and contact template and merged them into one to I have a listing of all employees and their personal information. I have another table with events that say what was taught or shown and so forth. I am trying to figure out the best way to have them register for these events.
Right now I have right now a registration form that has the employeeID and the eventID. The problem is I would like to be able to use a list view and just select the people who attended and with the press of a button have all the registrations be completed dynamically. As right now for each person I have to create a new entry. Hope I was not too confusing, and thanks for the time. Patrick
I have run into an issue with a basic DLookup. The database has grown in size and now we could have multiple entries, but I want it to return a certain one. So the information could be in it three times. Of course DLookup stops after the first one. How do I get it to loop to check the entire table? Someone mentioned to me to use a recordset, but how to write that as I have never used it before. Below is what I was using until this new request came up.
<code> Private Sub txtloan1_AfterUpdate() If IsNull(DLookup("[loan1]", _ "settlement", _ "[loan1]=""" & Me.txtloan1.Text & """ AND [status] = 'Open'")) = False Then Cancel = True MsgBox "Test", vbOKOnly, "Warning" End If End Sub </code>
This was also executing after the user entered the information within a text field. I did not want them to enter all the data and then have it come back as a duplicate.
Hi All I hope someone in this forum can help me with this databse issue: I want to load text file to .mdb MS access file. I understand that BULK INSERT is possible only when using SQL Server database - not MS Access like I do. Can someone advice what can be done ? Can you think of an alternate solution, which is also effective (not inserting each record one by one) ? BTW - I'm using ADO on visual studio 6 in order to access the database (not ADO.NET).
I am facing disaster after two months of solid work on this database, the deadline is the morning of the 25th and just when i thought i had got it all working i then put the main bulk of the data into the database and the main search query won't work, it freezes and ends with 'invalid argument', when its the same argument as before when there was less data in it. also the relationships window is acting somewhat strangely, i cant seem to move the tables around within it, this is the same for any databases on my computer (im guessing this could be something to do with my Access on my computer)...
A quick note about the search function. it runs three queries dependent on each other, the master query runs of a sub query which runs off an inititial query (this was found to be a very good way of dealing with null values in the database). two of the the tables it runs off is a one to many relationship with the main table so not all contacts will have that variable, but i don't see why this would affect the query. in short the first query in the sequence searches for a status of a contact, then the second uses these results to search for a sub status then the final query searches for publications contacts want from the sub status query. the three tables it runs from are contacts, status(which includes substatus) and publications.
well, it really is the 11th hour here, quarter past the 11th hour to be honest. short of throwing my computer out the window and running off to a tropical island does anyone have any ideas....????
