Dynamically creating table using form

Hello,

I am trying to dynamically creating tables in Access 2010.
I would like to get the info from the user (Table name, number&type of columns)
and then creating the table using an SQL Query.

I know how to write the query, but I need help with passing the arguments to the query.

Please help me out, Thanks


Post your answer or comment

comments powered by Disqus
Hi All,

How to Create tables using Transact sql statements in MS ACCESS 2007.

Please provide the solution with example.....

Thanks in Advance

Hi there, apologies if this question is a bit basic, I have searched for the answer to this question, and im sure it must be out there but i've had no luck after searching for quite a while.

How do you add data to a related table using a form?

I can add the record fine in the table (clicking the '+' sign at the side of the record brings a drop down with the empty fields to populate the related table)

However I was unsure of how to do this using forms. There is no '+' when i create a form for the parent table.

So for example my parent table (FrmVisitDetails) has 'VisitID' (Autonumber) as the primary key. And the child table (FrmWrkDetails) has 'VisitID' as primary key (number) I want to be able to add a record into FrmWrkDetails once FrmVisitDetails has been populated. I know you could do it as a subform on the original form, but I want them both on seperate forms as there are a lot of fields.

Thanks
Sean

I am trying to conduct a search through a fields in a table using Form Filter. I made a macro that's action is "DoMenuItem" and the command is "Filter by form". Every time I try to search for an object by typing in a value in a field (regular way and with wildcard characters), I never get results, even though those values exist. Can anyone tell me what I am doing wrong here? Also, is there any way to create a button on the form filter screen to hit instead of right clicking and hitting apply filter?
I am using excel 95 and thanks in advance.

Enrico

Hello all,
I am attempting to make a table using a Form to populate 2 of the 9 fields. The other 7 fields are all populated as title:"data" for what I need for the first record. Those all work fine. The other two fields I have are..
Transaction Date:[Forms]![Start Form]![TX Date]
Post Date:[Forms]![Start Form]![Post Date]

The problem I am having is that when run, all the fields get populated just fine except the date fields. Those end up null.

What am I missing?

SQL below
Code: SELECT "Header" AS Detail, "GJ" AS Line, "USD" AS Account, [Forms]![Start Form]![TX Date] AS Division, [Forms]![Start Form]![Post Date] AS Branch, "" AS Center, "" AS Debit, "" AS Credit, "" AS Adjustment INTO [export] I'm using this to prep a bunch of data for export as CSV, and the first line needs to have the data I'm setting in the make table query. All other data will be selected via query and appended to this one from existing tables.

Hello,

I am having a hard time trying to figure out how to do the following:

I have two separate tables in my DB. I would like to take a record from one table and swap it with a record from another table using a form. The form would recognize each record by a serial number. So essentially the records would just change location inside of the DB.

Any help would be appreciated.

Thanks!

hi,
I want to create a table like below
1st April, morning
1st april, afternoon
1st april, evening
1st april, night
2nd april, morning
2nd april, afternoon
2nd april, evening
2nd april, night
3rd april, morning
and so on

Is there anyway I can create such table using a query in some codes without doing manually?

I have a need to create tables using vba (DAO)

I have two tables, tblCreateTable & tblCreateTableFields

After I have selected the table I need to create in tblCreateTable, I need to create a recordest of the table's fields in tblCreateTableFields and create the table.

I understand how to select the table, and recreate the recordset

I am unsure of how to:
* Create the table
* loop through the recordset and add the fields to the table

Thanks in advance

I'm quite new to access. I need to create tables using queries - which I can do - no problems there. however, you know how you can define the display control (textbox, listbox, checkbox etc.) in the design view for each field? I would like to be able to specify that for each field using a create table query. Does anyone know how to do that? Any help appreciated!!

Here is what I am trying to do:
I have form, and a table which is created from this form. The data is entered a second time using the form to make sure there are no discrepancies.
Can someone suggest an easiest way to implement this? (create a new table (which is duplicate), and then compare them?

I currently have form that dynamically creates a SQL string (based on user selections) that filters a report. My users may select hundreds of records for this filter so there is a need to save the filter.

So, my solution was to create a make table query. Is there a way to prompt the user to either create a new table for the query to run against or select an existing table? This has to be per time so coding a table into the Make Table query would not work.

Any advice or assistance would be much appreciated.

I'm trying to create a table in an external database, that is based upon information found in a LINKED table from another external database. However, there are a LOT of fields, and I'd rather not have to specifically declare and type each field in a create table statement.

So, I thought, that i could Link the table temporarily and design a query off of it, that would include the specific fields I wish to preserve from the linked table, and then use that query as the source for a Select Into statement. Thus I do this as follows:


	Code:
	   tdfLink = CurrentDB.CreateTableDef("MyTable")
   tdfLink.Connect = ConnectStr
   tdfLink.SourceTableName = "Table1"
   CurrentDb.TableDefs.Append(tdfLink)
   CurrentDB.TableDefs.Refresh

' ... a bit later in another command button executed code

   set dbTmp = CreateDatabase("NewDb.mdb", dbLangGeneral)
   DoCmd.RunSQL "SELECT * INTO tbl_NewTable IN NewDb.mdb FROM qry_MyQuery;"

Now qry_MyQuery is designed off of the tabledef "MyTable" thus, when the program is dormant, there is no linked table and qry_MyQuery is inaccessible. (causes errors about the missing source table)
However, at Run-Time, the source table is present, and the query will display in a datasheet view subform that i have on my main form, but in the select into statement it creates an error.
Do I have to do EVERYTHING by hand with this, since the linked table is dynamically linked at run time?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

I have a table for sales contracts (contract table) of a product that is sold in gallons. These contracts are for extended time periods and require deliveries of orders against the main contract. I have a second table that is used to create delivery orders against the contracts (order table) that are directly related to the main contract. These orders are created for all deliveries that will be made over a set time period (i.e. next month). The orders table is used to track assignment of the orders to trucking companies for delivery, payment of the freight, and for allocation of the product against the main contract.

I want to be able to append records in the order table based on the contract record in the contracts table. I want to be able to create the number of records that is entered by the user. These records should have base data from the contract record.

There will be a one-to-many relationship between the contract table and the delivery order table.

Example:

I have a contract for 240,000 gallons in the Contract Table that is to be delivered for April, May and June. At the beginning of April I want to use a form to query the Contract Table and pull up this contract. I want to be able to automatically create 10 orders (user enters the number of orders they want to generate) for delivery in April against this contract. I want to be able to append 10 records to Delivery Order Table based on base information from Contracts Table. I also want to be able to have the user enter a Delivery Order Number that will be incremented by 1 for each record that will be created in the Delivery Order Table.


I am a novice at Access and very new to VBA so I need some help!

Hi

I have a form in access which contains a 6 x 4 table and some instructions (text) on loading of products.

I would like to create an email based on the template of this form.

How do you create tables in emails using VBA in access?


	Code:
	DoCmd.SendObject

thanks

I'm trying to create a table automatically (like when a button is clicked on the form), but am stuck. I'm not 100% sure if I need to use SQL or not to accomplish this. Really new to this stuff so please help me.

The follow is the code I have right now. When I tried to run this I get an error saying that the field INDIVIDUAL APR can't be found...

INDIVIDUAL APR is a form that the button is located. There are bunch data displayed on this particular form.

-----------------------------
'Session Average

Private Sub Session_Average()
On Error GoTo ErrorHandler
Dim tb20 As DAO.TableDef, table As DAO.Recordset, ENTRY_YEAR As String, YEAR As String
Dim session_year As String, yr As Integer, ssAvg As String, CURRENT_YEAR As Integer
tableName = Forms![INDIVIDUAL APR]!Box2
Set db = CurrentDb()
'Checking if table exists, if yes delete table
If ObjectExists(acTable, "SESSION AVERAGE") Then DoCmd.DeleteObject acTable, "SESSION AVERAGE"

'Create new table
Set tb20 = db.CreateTableDef("SESSION AVERAGE")
With tb20
.Fields.Append .CreateField("SESSION", dbText)
.Fields.Append .CreateField("SESSION AVERAGE", dbText)
End With

'Begin algorithm for calculating session average
ENTRY_YEAR = Form![INDIVIDUAL APR]!Box27
YEAR = Right$(ENTRY_YEAR, 2)
If YEAR = "00" Then
yr = 99
Else
yr = CInt(YEAR) - 1
End If

Do Until yr > yr + 5

'Algorithm for winter sessions
ssAvg = ""
session_year = ""
session_year = CStr(YEAR) + "W"
ssAvg = Round(DSum(" [GRADES] * [ACT CREDITS] ", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )") / DSum("[ACT CREDITS]", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)"), 2)
table.AddNew
table![SESSION] = session_year
table![SESSION AVERAGE] = ssAvg
table.Update

'Algorithm t summer sessions
ssAvg = ""
session_year = ""
session_year = CStr(YEAR) + "S"
ssAvg = Round(DSum(" [GRADES] * [ACT CREDITS] ", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL )") / DSum("[ACT CREDITS]", tableName, "[SESSION] = '" & session_year & "' AND ([GRADES] LIKE '[0-9]*' OR [GRADES] = NULL)"), 2)
table.AddNew
table![SESSION] = session_year
table![SESSION AVERAGE] = ssAvg
table.Update

yr = yr + 1
Loop
Exit Sub

ErrorHandler:
MsgBox Error(Err)
Exit Sub

End Sub
-----------------------------------------

Here's a similar code that actually works:

Private Sub AU_query()
On Error GoTo ErrorHandler
Dim qdf1 As QueryDef, tbl3 As DAO.TableDef, table As DAO.Recordset
Dim strSQL2 As String, AUREPORT As String
tableName = Forms![INDIVIDUAL APR]!Box2
Set db = CurrentDb()
If ObjectExists(acTable, "AU REPORT") Then DoCmd.DeleteObject acTable, "AU REPORT"

Set tbl = db.OpenRecordset("SELECT * FROM [" & tableName & "];")

'Create new table.
Set tbl3 = db.CreateTableDef("AU REPORT")
With tbl3
.Fields.Append .CreateField("REQ COURSE", dbText)
.Fields.Append .CreateField("ACT COURSE", dbText)
.Fields.Append .CreateField("OPTION", dbText)
.Fields.Append .CreateField("CREDITS", dbText)
.Fields.Append .CreateField("SESSION", dbText)
.Fields.Append .CreateField("YEAR", dbText)
.Fields.Append .CreateField("MATH AU", dbText)
.Fields.Append .CreateField("BAS SCI AU", dbText)
.Fields.Append .CreateField("COMP STUD AU", dbText)
.Fields.Append .CreateField("ENG DES AU", dbText)
.Fields.Append .CreateField("ENG SCI AU", dbText)

End With

db.TableDefs.Append tbl3
Set table = db.OpenRecordset("AU REPORT", dbOpenTable)
If (tbl.RecordCount = 0) Then

Exit Sub
Else

tbl.MoveFirst
Do Until tbl.EOF
table.AddNew
table![REQ COURSE] = tbl![REQ COURSE]
table![ACT COURSE] = tbl![ACT COURSE]
table![OPTION] = tbl![OPTION]
table![CREDITS] = tbl![ACT CREDITS]
table![SESSION] = tbl![SESSION]
table![YEAR] = tbl![YEAR]
calYear = session_edit(tbl![SESSION])
If (Not (calYear = "20" Or calYear = "19")) Then

Set Autable = db.OpenRecordset("SELECT * FROM [AU " & calYear & "];")
checkAU = "[COURSE] = '" & tbl![ACT COURSE] & "'"
Autable.FindFirst checkAU
If (Not (Autable.NoMatch)) Then

table![MATH AU] = Autable![MATH AU]
table![BAS SCI AU] = Autable![BAS SCI AU]
table![COMP STUD AU] = Autable![COMP STUD AU]
table![ENG DES AU] = Autable![ENG DES AU]
table![ENG SCI AU] = Autable![ENG SCI AU]

End If
End If
table.Update
tbl.MoveNext
Loop
End If

Exit Sub
ErrorHandler:
MsgBox Error(Err)
If ObjectExists(acTable, "AU REPORT") Then DoCmd.DeleteObject acTable, "AU REPORT"
Exit Sub

End Sub

---------------------

Not sure why there are bunch variables declared in the 2nd one that aren't even used in the sub.

Thanx everyone!

You're about to witness a grown man cry.

Thanks to this forum I've managed to put together my database. I believe it's fairly good (for me at least. I'm sure you Access MVPs could whip something twice as good in half the time!).

Anyway. My table done, I thought I'd design a form. Easy peasy!
So I thought.
Nothing has brought me quite as close to killing myself.

Let me give you a small example of my problem.

Tables:

tblCharity
CharityID
CharityName
TitlesID
FirstName
LastName
PositionID

tblTitles
TitlesID
Title

tblPosition
PositionID
Position

OK. So my relationships are all set up. Everything should work. I go into designing my form and that's where it all goes wrong. I'm going to take it slow and explain step by step. I'm not doing this because I think you're all as thick as I am (that's impossible) but because I want to make sure I explain it properly because the Samaritans are just about fed up with listening to me.

- I create a blank form.
- I insert the fields CharityName, FirstName and LastName.
- I go into Form View and check it works: it does. I try and enter some text. It does. I don't create any records though.

- On the right hand side of my window I see the Field list. Under "Fields avaliable in related tables" I see tblTitles and tblPosition. I click the little plus sign next to them and am shown the fields that those tables contain.

- I drag over Title and then Position.
- I go into Form View to test.
- I can enter information into the Title & Position fields. I cannot enter anything into the CharityName, FirstName and LastName. If I try to do so I am given the error message: "Cannot add record(s); join key of table 'tblCharity' is not in recordset'.

- I cry.

Now I have found that by using a subform I can input all the data needed but I think the subform layout is awfully ugly for something as simple as Title and Position (especially a "Mr" and then "Chairman" or something like that).

Now I've actually got 12 tables, but basically I need to know how I can put fields from different tables onto one form.

I hope I've explained that well. If not I'll zip up the DB and post it if it's easier.

Hi

I am trying to create/populate a table from a form based on data entered in that form.

ie. Data is entered into the form either by direct input or from data stored in table X, this data is then used to create/populate a new table with an unknown number of data points (this depends on what is entered in the form). The new table is then displayed within the form.

I am fairly new to access, is this possible?

Cheers

I am trying to create an access form to manage records of photographs in a main table.

I also have about 10 other tables each corresponding to a specific picture library containing records of which images have been sent to them.

In the main form for each image archive record I want to embed a list of these libraries and indicate whether or not the image has been sent to them. Embedding a form for each library is doable but the arrangement isn't really ideal or particularly ergonomic. If an image has not been sent to a specific library I want to add a button which will open the main form for that library to add a new record (otherwise I want the button to be disabled or hidden).

I have a similar function using Asp.net web pages running off IIS on my PC but I want to do the same job entirely in MS Access (for portability) without needing to have IIS running.

In an ASP script I would obviously embed the sql statement in the code and use an If...End If to generate dynamic page content based on whether or not the query returns a result but not sure what the approach would be to do a similar function in an access form.

I'm wondering if there is any way I can call a subroutine/function from within the Private Sub Form_Current() that will execute a query for each image library table in turn, display the relevant information in text labels rather than text boxes and set the button (to open the form for the specific library) status as appropriate.

Hello. I am pretty new to access and VB so I'm going to describe my database design and then give you the problem so you can tell me if there might be a better way to do it.

We are running a chess club at my school. We want to keep stats with access so I made a table with 8 fields. ID, First Name, Last Name, Rank, Wins, Losses, Draws and Win percentage.

I then created a form with two lists, and a frame with 3 buttons with the options Player 1 wins,
players 2 wins and draw. I added a button at the bottom that serves to update the database.

What I want it to do is Use the selected radio button to add a win/loss/draw to the appropriate records which are selected in each field. I will then have to re-organize the ranks for most of the entries because the rules are custom and pretty strange. When you beat a player with a higher rank than you, you move up half the difference in ranks, which will have to force all the records bellow to move down, while the player you played loses only one rank. This makes it easy to get to the middle of the list and progressively harder to get up to the top. I have no idea how to program the sub for the button click though.

Any help is appreciated.

Thank you,
Chris

I have a series of forms and subforms which cascade to dynamically create a checklist form of certain questions and answers dependant on the choices selected by the user.

This works by dynamically populating the form with whatever questions are required from the relevant tables

The check-list actually works quite well however I can only get the form to display 1 question at a time in single form view or all questions at a time for continuous form view. I do not want to use datasheet view

So therefore is there more customisation I can do with continuous view in code to show a certain number of questions per page for example, I don't like the scroll options I want to do everything with command buttons?

Thanks

Code: NewLogin = "CREATE LOGIN " & Me.txtUserID & " WITH PASSWORD = '" & Me.txtPassword & "'; USE myDB;" _ & "CREATE USER " & Me.txtUserID & " FOR LOGIN " & Me.txtUserID & "; EXEC sp_addrolemember 'myDB_admin', '" & Me.txtUserID & "'" dbs.Execute NewLogin This code results in "Syntax error in CREATE TABLE statement", even though I'm not using a CREATE TABLE.

A print statement generates this:

Code: CREATE LOGIN admin2 WITH PASSWORD = 'password'; USE myDB;CREATE USER admin2 FOR LOGIN admin2; EXEC sp_addrolemember 'myDB_admin', 'admin2' Which, according to my SQL Server, is valid SQL code. When run as a query server side it pops up the login, user, and role just fine.

According to this note here: http://msdn.microsoft.com/en-us/library/ff837200.aspx MS Access doesn't directly support this form of CREATE statements though? It says refer to the DAO Create, but that page isn't filled out on MSDN. Therefore I'm a little confused as to what I need to do.

I'm trying to create SQL server logins through this Access DB. I know that is horribly, horribly insecure, and probably why it won't allow me to do it. Is there any way around this? Is there a different method of doing this that is preferred? Should I be smacked in the head for even trying this? Keep in mind I still don't know much about SQL Server, but definitely enough to get me in trouble.

Hi everyone,

I created a Database with 10 tables, Case number is PK in Table one and all other tables are related by Case Number but the case number is not PK in the other 9 tables. Is there a way to populate Case number in all 10 tables using one field on a single form? I am a beginner and do not know much. Any help will be highly appreciated.

Thanks

Hi I searched a lot for this problem (Run-time error 3134: Syntax error in INSERT INTO statement) , please help me if you know the solution:

I have a table created with some fields such as AQ Code, Process Category, Process Type (Combo box), Notes, Start Date, End Date, Updated By, Update. I have also created a blank form with same fields to make entries into this table.

The VBA code I am using is as follows:
Private Sub cmdAdd_Click()
'add data to table
CurrentDb.Execute "INSERT INTO tblProcesses(AQ Code, Process Category, Process Type, Notes, Startdate, Enddate, Updated By, Update) VALUES("' & Me.txtCode & "','" & Me.txtCategory & "','" & _
Me.cboType" & "','" & Me.txtNotes & "','" & Format(txtStartdate.values, "mm/dd/yyyy") & " #,#" & Format(txtEndDate.Value, "mm/dd/yyyy") & "#,#" & Me.txtUpdate & " #)"

'clear form
cmdClear_Click
'refresh data in list on form
frmProcessesSub.Form.Requery
End Sub


Private Sub cmdClear_Click()
Me.txtCode = ""
Me.txtCategory = ""
Me.cboType = ""
Me.txtNotes = ""
Me.txtStartdate = ""
Me.txtEndDate = ""
Me.txtUpdatedBy = ""
Me.txtUpdate = ""

'focus in AQ Code text box
Me.txtCode.SetFocus
End Sub


Private Sub cmdClose_Click()
DoCmd.Close
End Sub

I am relatively new to Access and have been struggling with this for a while. I can use any help I can get. Thanks in advance!

I am creating a database to track patient case loads for individual providers. Some patients are seen by multiple providers. The goal is to have the patient in the database only once, but have the option to list multiple providers within the record.

I have created a Select Query that links to an ODBC table to pull patient demographic information from the electronic medical record system. The query pulls all of the data that I need correctly. I have created a Form titled "Home". The "Home" Form has buttons that run different Macros. I want to be able to click one button on the "Home" Form to run the demographic query and search for a specific patient ID. I was thinking that it would be helpful if I could set it up so that, on click, I could enter the patient ID and it would search the linked ODBC connection for that patient's demographics and then automatically add that patient to the patient list table, along with all of their demographic information that I need.

I hope this makes sense to someone. Like I said, I've been beating my head against the wall trying to figure this out and I just can't do it on my own. Thank you!!!

query has 4 columns, first 3 from 1 table, last from a second table.
the query works fine.

when using the report wizard, i select to make a report from that query, but after selecting all needed field, the wizard askes "how do you want to view your data" and options "by table1" and "by table2"...

choosing one and finishing the wizard's last steps, it creates a new form instead of a report.

Am I doing something wrong?
As I understand, the report gets created from a query, not from a table...


Not finding an answer? Try a Google search.