I am a total beginner to Microsoft Access 2010. I need a form that allows a user add items from as list to a list. The items can be added until initial list is exhausted. Here's the most basic example I can give. Lets suppose that two persons attend a car show. As they are leaving, they are asked their name, and what cars they saw. First person saw only two cars. The second one saw fifty cars. On the form, they would write their name one time (at the top of the page) and then they have to fill the list of cars they saw from the list of all cars. They would not write their name next to each car. How do I create an equivalent MS Access 2010 form?

I am REALLY very new to this so please excuse my ignorance, but I could really use some help! I've got two tables: BillsFiled and Authors On BillsFiled, there are the columns: ID, Symbol, Filedate, Type, Author, Title, Summary, among others This is populated from an imported Excel file which is filled in by an outside source which cares not for my needs of referential integrity which means that the "Author" field can contain more than one entry, such as: ID-Symbol-------Filedate------Type------------Author--------------------Title--------------------Summary 1 - s001 ---- 11/18/2014 --- Resolution -- Smith ; Stevens ----- A Resolution to... ------- yadayada 2 - h002 ---- 11/18/2014 --- Bill ----------- Diaz -------------------- A Bill to... ----------------- yadayada 3 - s002 ---- 11/18/2014 ---- Bill --------- Ryan ; Smith ; Harris-- A bill... -------------------- yadayada Because I need a record linking "Author" to "Symbol" I also have the table: Authors. This just contains the columns: Symbol and Author. It is my most fervent desire to run a query or some vba or a macro or something to split the multiple entries in "BillFiled" to append "Authors" such as: Symbol - Author s001 Smith s001 Stevens h002 Diaz s002 Ryan s002 Smith s002 Harris I found a string of code from about a year ago offered on this site in response to a very similar question from another user and have tried using it, but am getting strange results. The code (updated with my table and column names) was: Sub SplitIt() Dim rstBillFiled As Recordset Dim rstAuthors As Recordset Dim Items() As String ' use dbOpenSnapshot to open the source table READ-ONLY Set rstBillFiled = CurrentDb.OpenRecordset( _ "SELECT Symbol, Author FROM BillFiled;" _ , dbOpenSnapshot) ' use dbOpenDynaset to open the destination table READ-WRITE Set rstAuthors = CurrentDb.OpenRecordset( _ "SELECT Symbol, Author FROM BillFiled;" _ , dbOpenDynaset) With rstBillFiled ' .BOF is Beginning of the table ' .EOF is End of the table ' Checking if both are false means there are records in the ' source table If Not (.BOF And .EOF) Then ' get the first record from the source table .MoveFirst Do ' if Author is NULL (empty) If Nz(!Author, "") = "" Then ' add a new record into the destination table ' with data from the source table for Symbol rstAuthors.AddNew rstAuthors!Symbol = rstBillFiled!Symbol ' set Author to NULL (empty) rstAuthors!Author = Null ' save the new record rstAuthors.Update Else ' if Author IS NOT NULL ' convert Author into an array of strings Items = Split(Nz(!Author, ""), ";") ' loop through the array of strings For a = LBound(Items) To UBound(Items) rstAuthors.AddNew rstAuthors!Symbol = rstBillFiled!Symbol ' Author is set to the current item in the array rstAuthors!Author = Items(a) rstAuthors.Update Next a End If ' load the next record from the source table .MoveNext ' repeat until the end of the source table is reached Loop Until .EOF End If ' close the source table rstBillFiled.Close End With ' close the destination table rstAuthors.Close End Sub With the instruction to then run SplitIt from the Immediate window. When I do that, it does collect and split the records but all of them are added into "BillFiled" rather than "Authors" Also it repeats the action with the first two records such that if there are 4 records in the source table attributable to 6 authors in total, it will add 8 new records to the source table and nothing to the destination table. As I said in the beginning, I really don't know what I'm doing and don't have any experience with code, but am trying to build this database to help a family member with their business who doesn't have time to learn how to. I have some time but sadly no knowledge so if anyone out there could take pity on me and lend your experienced eye to this, I would be so grateful! Thanks!

I have a Microsoft Access 2010 database file of type ADP (which can be compiled to an ADE file). This front-end application interacts with a Microsoft SQL Server in the backend. This application works fine in the current environment, but is having trouble in a new environment. In the new environment, the datasheet-type form we use loads correctly. The query in the form RecordSource is: > SELECT t1.columnone, t2.columntwo > FROM tableone t1 > INNER JOIN tabletwo t2 ON t2.columntwo = t1.columntwo > WHERE t1.condition = 'test' This will then fail, when trying to use the MS Access form to add a new record to existing records. The error is: > 'SELECT t1.columnone, t2.columntwo FROM tableone t1 INNER JOIN tabletwo t2 ON t2.columntwo = t1.columntwo WHERE t1.condition = 'test'**1**'@* You misspelled the object name. Check for missing > underscores (_) or other punctuation, and make sure you didn't enter > leading spaces. * You tried to open a linked table, but the file > containing Upon closer inspection, we can see that the number 1 has been erroneously added to the end of the query, but can confirm that the number 1 doesn't appear anywhere in the source code, so it is unclear why it is being added at runtime. We can confirm that the MS Access version is exactly same between versions and the SQL Server database is also the same. Based on research, we tried removing the dbo prefix from MS Access code, so that the code just shows tablename in queries (and no dbo. prefix). However, the issue persists. EDIT: Symptoms are the same as this article: https://stackoverflow.com/questions/13676293/access-2010-adp-appending-a-1-to-the-end-of-object-names , however the solution in that case is not relevant here (there is not multiple copies of MS Office/Access installed).

I have two tables in access with a common ID number in both. One table has mail to info and the other table has account information. The account information can have multiple listings per ID. The mail to info has one listing per ID. I need to combine the two tables to create a new table with 1 row per ID but containing multiple columns with the added account information. How do I do that?

We are using Access .adp (Access 2010) frontend in combination with SQL Server 2014 backend. At the moment we have a big performance problem with the SQL function permissions(). We have ca. 400 Users on our application. The logon takes 50+ Seconds for an normal user. Sysadmins logon takes 3 seconds. Do you have a solution for this problem?

Using Microsoft Access, I have a number of records that have duplicated field-1. I need to form a query such that the duplicate records get unique ; BUT the next filed-2 data must be seen in columns next to each other the same no. of times. For example, now I have a table like this: field-1 | field-2 1 | date1 2 | date1 1 | date2 3 | date1 3 | date2 1 | date3 And query output needed: field-1 | (1st-duplicate) | (2nd-duplicate) | (3rd-duplicate) | .... (nth-duplicate) 1 | date1 | date2 | date3 2 | date1 3 | date1 | date2 How I can do that ?

Hi I Inserted A Select MaxID+1 Data and want to retrieve what was that ID I inserted the Following is the codes Im trying to Insert data from Excel to Ms Access and Automatically Retrieve that Data. For MultiUser Purpose I need to retrieve specifically the data I inserted.

Sub PostData()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset classe here
Dim dbPath
Dim x As Long, i As Long

'add error handling
On Error GoTo errHandler:

dbPath = Sheets("Sheet3").Range("h1").Value

Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

Set rst = New ADODB.Recordset 'assign memory to the recordset
Sql = "INSERT INTO DvID(DVnumber)SELECT Max(DVNumber)+1 FROM DvID "
rst.Open Sql, cnn
Sheet3.Range("A2").CopyFromRecordset rst
rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

On Error GoTo 0
Exit Sub
errHandler:

Set rst = Nothing
Set cnn = Nothing

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export_Data"
End Sub

I have created an accdb with customers and products tables. Now, I want to know how to store the sales order details in the Sales table, WHEN each sale has multiple products, each with different quantity and price. Like Order S01 -> Customer X -> Products a, b, c -> qty 1, 2, 3 -> price 100, 200, 300 I am planning to insert and update sales details from my excel sheet. 1. As my order number will be the unique primary key, I think its illogical to repeat order numbers for each product. Like, S01 | X | a | 1 | 100 S01 | X | b | 2 | 200 ... 2. Currently, I have implemented this as comma-separated values in a field, but it seems it is not a good practice to store like this. It involves lots of unnecessary scripting to unwrap and wrap the data and not at all intuitive. Like, S01 | X | a,b,c | 1,2,3 | 100,200,300 3. I cannot create a separate table for each product to store the sales and purchase data(if this is called normalization). Too many tables, not good practice. Like, Table a S01| X | 1 | 100 Table b S01| X | 2 | 200 ... So, whats the most optimized and intuitive way to store such data and retrieve it.

I have a make table that figures daily pride and assigns the current date to each line. I want to be able to run a query to pull all pride numbers from 06/01/2016 to 06/30/2016 and then run a query to find the dates in which there was no pride information entered if we could factor out weekends that would be even better.

I have a front end database that I want to restrict user access to only enter data into a form. I do not want them to be able to scroll through other entries via the form. How can I restrict this in Access 2007-2010?

So I get how to connect Access to SQL Server, and I understand that the tables have to be linked, but my problem is security. If I link the tables from SQL Server into Access, then anyone who comes into the Access forms to enter data can ultimately get at the tables via the link can't they? I know that newer Access versions have no user security, but is there any way to restrict what data they see in some of the tables, or do I need to do that on the back end in SQL Server Express?

Because my table has over 3.5 million records I'm having issues with changing the data type in certain fields in the original table (Table A). Since it's so big I can't simply copy and paste the entire table and if I went with the 65,000 record limit, I'd have to do it 54 times, which I'd rather not do. I created a "Copy of A" (structure only), fixed the data type and tried an append query, but that didn't work either. It couldn't open the table. Anyone have any suggestions?

In my office, we often use MS Access for many applications (eg creating reports, data analysis and data processing) because it is convenient to use (easy data import, fast query building, easy automatization with VBA) Unfortunately, it starts to be insufficient for me due to the size limitations of the database file. This is one thing. Another thing is that Access is often unable to execute my complex SQL queries (I omit that the SQL used by Access is limited). Is anyone out there aware of any tools similar to MS Access that can be used with databases over 2GB? Possibly, I am considering using Access as a front-end, but what about the back-end? Can you recommend something?

I have an mdb file that has some large tables in it (greater than 65000 rows). I used to not have any problems duplicated the table by copying and pasting but just recently I started getting a message about the size of the clipboard not being able to hold more than 65000 lines. What's weird is the operation works on another computer with the same version of Access (32bit 2010) and the same table and doesn't report this error message. It just started reporting this message on the "problematic" computer recently, and nothing has changes as far as I know except for Windows automatic updates. Any help appreciated.

I was just updated to Access 2016, while my users are still at Access 2010. I opened our database in MS Access 2016 once, didn't make any changes, and closed it out, and now all the users are having errors for **ANY** basic VBA code, including: StrConv (Me.FormField, vbProperCase) DoCmd.GoToRecord DoCmd.RunCommand acCmdSaveRecord etc. However, it works fine on my end. Totally broken for all my users on Access 2010. Is there any way to fix this? I am thinking, even if I go back to Access 2010, if these are errors for code that would normally work fine in either version, how to I resolve these errors? Tried a compact and repair, no effect. Here's an example: In Access 2010, when the AfterUpdate() event is triggered on a textbox that has the StrConv (Me.FormField, vbProperCase) function, it brings up the VBA debugger. The error is: **Compile error: cannot find project or library.** Previously, the exact same function worked fine in access 2010, and there have been no changes to this code. It works fine in a backup copy from about 15 days ago.

When I run the upsizing module it goes through the motions but the report at the end says >"table was skipped or export failed" for each of the tables. I can't find a solution for this. I can't figure out why this is?

Is it possible to create a query and ask the following? I have two tables (each one has one column with data) and I want to obtain as a result all data from the first table MINUS the data from the second table.

I have 2 tables: - Contacts - Helpers The Helper field (which is linked to the Helper table) in Contacts is a multi-valued field so more than one helper can be selected and displayed in a report. Each Helper is linked to more than 1 Contact. I'm trying to create a query selecting all contacts for one helper, and display all helpers for the resulting contacts. I think I am off-base with multi-valued fields, but cannot find another solution. It is in Access 2010. Tables do not have multi-value fields. They are: ### Contacts ContactID Surname Email ### Helper HelperID Name

I have an Excel spreadsheet that is filled with a ton of data which is organized by column (a column for names, a column for occupation etc.). I have recently moved all of this data into Microsoft Access 2010 to work with, but I do require to move this data back and forth between Access and Excel. Since then, I have made a lot of changes to the data and I now want to export this data back from Access into Excel, with the data appearing in the same columns that they were imported in from. I have tried exporting the data back into Excel, but it doesn't look the same as the excel file that I originally exported the data from. I was wondering if there is a way to export all of the data in Access back into Excel so that it is formatted exactly the same way it was before it was exported into Access Sorry if this is a noobish question, I'm new to Access and don't have much experience with it yet. Thanks

I have table (SQL MS ACCESS) COUNTRY NO_OF_PAGES FRANCE 2 UK 4 GERMANY 1 I want output COUNTRY NO_OF_PAGES FRANCE 1 FRANCE 2 UK 1 UK 2 UK 3 UK 4 GERMANY 1