Create Table From A Recordset
Mar 27, 2007
Hi,
I'm trying to create a new table from another existing two in vba. I'm using this code without success... Any help will be gratefully taken.
---------------------
Dim strSQL as string
Dim tablaSuma As DAO.TableDef
Dim rst As DAO.Recordset
strSQL = "SELECT * FROM Necesidades_TRS1, Pedidos WHERE Pedidos.Código=Necesidades_TRS1.Código"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
Set tablaSuma = CurrentDb.CreateTableDef("TablaSum")
For Each Campo In rst.Fields
tablaSuma.CreateField(Campo.Name, DB_SINGLE) = Campo
Next
----------------------
Mike
View Replies
ADVERTISEMENT
Aug 17, 2007
from a table with fields userID and Date (in which any userID has multiple records with different Date values, and other fields) i have built a select query based on Date = one specific value. can anyone give an explanation (for beginners), through a sample code, that expands the query recordset so that the new recordset includes all records per userID that qualified in the prior recordset? below is an example. thank you!
Table with records:
userID / Date
u1 / d1
u1 / d2
u2 / d1
u2 / d2
u3 / d2
...
1st Select Query (where Date = d1):
u1 / d1
u2 / d1
Desired 2nd Query based off 1st Query (where ?):
u1 / d1
u1 / d2
u2 / d1
u2 / d2
View 3 Replies
View Related
Jan 17, 2005
What is the easiest way to get the results from a query?
I now create an empty form and set the sql-statement as the recordsource, but this means I can't open two forms simultaniously because they use the same subform.
I know how to creat an ado-recordset, but this would mean the client needs to install extra dll's, my client doesn't have those (I want the program to work on a pc that has access installed and nothing more).
There must be an easier way...
Another problem I have is that when access is closed the MSACCESS proces keeps running, is this a bug, can I have caused this?
With any other access file this is not the case, but they do not have an autoexec.
Please help
Yvonne
View 1 Replies
View Related
Jul 21, 2007
Why does the following code generate an error. All i want to do is open a recordset which I thought would be straightforward. (novice Programmer, new to access vba). The set statement in the ComboProduct event generates the error.
Option Compare Database
Dim Company As String
Private dbaProposal As DAO.Database
Dim EffectiveDate As Date
Dim Product As String
Private rstProposal As DAO.Recordset
Private Sub ComboProduct_AfterUpdate()
Product = ComboProduct.Value
Set rstProposal = dbsProposal.OpenRecordset("SELECT * FROM Proposals WHERE Proposals.[Group Name]='" & Company & "' AND Proposals.[Effective Date]=#" & EffectiveDate & "# AND Proposals.Product='" & Product & "'")
End Sub
Private Sub Form_Load()
Set dbsProposal = DBEngine.OpenDatabase("Database1.accdb")
End Sub
View 2 Replies
View Related
Jun 2, 2005
I have a table called Contacts and a form with two combo boxes for searching for records either by name or property name. The following code works fine for finding the first record, but I want to be able to show on the form all (and only) those records which match the combo box entry. Currently rowsource for name box is:
************************************************** *
SELECT [Last Name1] FROM Contacts UNION SELECT [Last Name2] FROM Contacts
ORDER BY Contacts.[Last Name1];
************************************************** *
and code is:
************************************************** *
Private Sub Combo214_AfterUpdate()
' Find the record that matches the control for Last Name search
Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[Last Name1]= '" & Me.[Combo214] & "' OR [Last Name2]= '" & Me.[Combo214] & "'"
rs.FindNext "[Last Name1]= '" & Me.[Combo214] & "' OR [Last Name2]= '" & Me.[Combo214] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Combo214.Value = ""
txtFirstName1.SetFocus
End Sub
************************************************** *
Rowsource for property box is:
************************************************** *
SELECT Contacts.PropertyID, Contacts.PropertyName
FROM Contacts
ORDER BY Contacts.PropertyName;
************************************************** *
and code is:
************************************************** *
Private Sub Combo212_AfterUpdate()
' Find the record that matches the control for Property Name search
Dim rs As dao.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[PropertyID] = " & Str(Nz(Me![Combo212], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
Combo212.Value = ""
cboPropertyName.SetFocus
End Sub
************************************************** **
Any help is appreciated!
View 7 Replies
View Related
Sep 3, 2014
Is it possible to create a record set from a list box?
I have two list boxes list1 (customers) the can transfer records to list2. I want to take all records from list2 and use it to open a report, using customer id as where clause in my docmd.openreport statement.
View 4 Replies
View Related
Mar 17, 2014
I have some code that takes from a query, an email address and sends out an email.Due the security warning that happens for each individual email, i want to create ONE email for all email address in the query and populate them to the BCC section of the sendobject.Here is the code that I currently use
Code:
Private Sub cmdSendEmail_Click()
Dim MyDB As DAO.Database
Dim rsEmail As DAO.Recordset
Dim sToName As String
Dim sSubject As String
Dim sMessageBody As String
[code]...
I want to populate the BCC field with multiple emails addresses seperated by ":" and send just one email.
View 4 Replies
View Related
May 5, 2013
I need to come up with a way to derive field X (see below) in a query.
For each change in field A, Set X=1
For each change in field B, X=X+1
The below table shows what the query results should look like.
A
X
B
[code]....
View 2 Replies
View Related
Aug 14, 2006
Can anyone help me to create an accumulative balance in a table if the invoice is the same. For example:
Inv # Transaction Amount Balance
123456 10 10
123456 20 30
123456 15 45
321654 10 10
321654 35 45
321654 10 55
Thanks
View 2 Replies
View Related
Jun 24, 2014
Is there a way that i can put an ADODB.recordset in a new table using VBA in MS access 2013? The record set is dynamic, hence a new table should get created every time.
View 1 Replies
View Related
Aug 14, 2015
Special situation: The SQL Server Linked Server across the country is linked to a Read Only Oracle DB. This data pull works perfectly and populates the Subform.
The problem is that Oracle can take 3 to 6 seconds to retrieve the single record depending on the network traffic through a small pipe.
The code below shows the RecordSource for the SubForm. clicking on a list box supplies the value. Then 3 to 6 seconds later, the subform populates.
The actual Recordset for this Recordsource is needed to conduct Validation on each field. Normally this would be on SQL Server, I might just create a Recordset Oject and run this SQL statement again in 1 milisecond. In this case, it will probably take an additional 3 to 6 seconds. Avoiding another lengthy round-trip to Oracle would be prefered.
Goal: How does one grab, clone, or other wise reference the existing recordset for the SubForm?
Note: Immediate Window - One single field can be returned quickly
There are 48 fields that need validation - is there a way to reference the entire recordset?
Immediate Window during Break Mode:
? me.fsubsrNavSHLBHL("NavSH_QQ")
NESE ' this is the correct value for the current recordsource
Set a breakpoint right after the line:
fsubsrNavSHLBHL.Form.RecordSource = "Select * from vsrNavigatorSHLBHL where Well_ID =" & txtNavWellID.Value
Immediate Window:
? me.fsubsrNavSHLBHL.Form.RecordSource
Select * from vsrNavigatorSHLBHL where Well_ID =91229
View 4 Replies
View Related
Jul 9, 2005
In a Library database, there is a form about Books Lends o returns (table: MovBooks) When someone needs a books, I need verify if the book is lended or not. (Table: Books, field: Status -Yes/No-) and update if its free
I want to resolve this problem using Recordset.
Thank you very much
View 2 Replies
View Related
Oct 28, 2004
hello there,
just curious if it was possible to populate an empty tables' fields from a recordset that had been defined earlier in VBA under the criteria of a bit of sql code?
cheers.
View 2 Replies
View Related
Oct 20, 2004
I have rather complex select statement (at least for me) that produces a summary of how a team scored in a league competition that week. I would not even worry about this issue, except that people float around as alternates and I deduced it would be easier to compute and store scores for a team the week they shot with a given group of people versus keeping track of who shot on what team over a 10 week period.
The selection takes 40 individual scoring records and consolidates them into 10 team summary records. I have verified that the query works, but implementing it has turned into a problem.
Problem one: my knowledge of recordsets is almost purely theoretical at this point, having never worked with them. Therefore commands and structures are a problem - mainly, I'm not sure what code is needed to post a recordset to a table
Problem two: the select statement that works as an Access query is bombing in VB
My code to date:
Dim RS As Recordset
Dim DB As Database
Dim strSQL As String
Dim inpWeekNum As Integer
inpWeekNum = 0
'InputBox "Enter Week Number" 'eventually request week number from user. an integer from 0-9
'strSQL = "SELECT tblRoster.* FROM tblRoster WHERE (NIGHT = 'Fri');" 'a test str. this worked.
strSQL = "SELECT tblRoster.TEAM, tblScores.WeekNo, " & _
"Sum([A1T1]+[A1T2]+[A1T3]+[A2T1]+[A2T2]+[A2T3]+[A3T1]+[A3T2]+[A3T3]) AS TeamTotal," & _
"Sum([A1T2X]+[A1T3X]+[A2T2X]+[A2T3X]+[A3T2X]+[A3T3X]) AS TeamXs" & _
"FROM tblRoster LEFT JOIN tblScores ON tblRoster.HEDR = tblScores.HEDR" & _
"GROUP BY tblRoster.TEAM, tblScores.WeekNo HAVING (((tblScores.WeekNo)=0))" 'hardwired week number
' "GROUP BY tblRoster.TEAM, tblScores.WeekNo HAVING (((tblScores.WeekNo)=inpWeekNum))" 'user prompted week number
Set DB = CurrentDb()
Set RS = DB.OpenRecordset(strSQL)
Do While Not RS.EOF
'Appending Code here... 'obviously missing code, but I know that it does cycle through as I would expect it to.
RS.MoveNext
Loop
'This was a previous test based on a canned qry I had saved (that resembles the above qry.
' this successfully posted, but only one record
'DoCmd.RunSQL "INSERT INTO [tblTeamScores](TeamNo, WeekNo, TeamTotal, TeamXs) " &_
' "VALUES (" & TEAM & ", " & WeekNo & ", " & TeamTotal & ", " & TeamXs & ")"
The select statement correctly produces this output in Access (for Week 0):
TEAM WeekNo TeamTotal TeamXs (how does one properly post a table in this interface?)
1 0 2397 182 0 3152 333 0 3292 664 0 2014 135 0 2836 186 0 3652 577 0 2024 198 0 1108 29 0 2630 2210 0 1977 11
Anyone out there have some suggestions? Am I even on the right track?
Thanks.
-Brian.
View 7 Replies
View Related
Apr 13, 2005
HI all
I have form containing some filtered records. What code do I need to put on a button to trigger a make-table query; the make-table thus containing only the current filtered records (not the entire database).
The following will use the entire database:
Dim stDocName As String
stDocName = "qryMakeTableCompanyID"
DoCmd.OpenQuery stDocName, acNormal, acEdit
Thanks
John
View 1 Replies
View Related
Feb 25, 2006
Hi all,
I have a form with around 10 checkboxes which serve as a filter option...now, when I hit my cmdFilter button it works well with a simple MsgBox !Ime showing all the filtered names...now, I want to put the results into a table tblTemp so that I could show the results in my subform. I've tried with making a sql string something like "INSERT INTO tblTemp..." but it's still empty.:confused:
Since this table will serve as a one time data, I will need to delete all records when I hit the Filter button next time...so, how do I send my recordset data into my table.
I hope this sounds understandable...
Thanks a lot,
Daniel
View 14 Replies
View Related
Jul 21, 2007
I'm experimenting in MS Access VBA reading from one MS Access application to another as visible in the code below. My problem is that I'm reading a recordset from an Access file and want to take that recordset (rec) to create a table in the current databank. If I run an cmd.commandtext "make table query" it runs the query across the connection and places the table in the remote database (as expected). Can I take the recordset and create a local table using ADODB command or is there a better approach. (I should note that the remote MS Access file does not have an .mdb suffix) Thanks in advance.
Sub get_indbyind()
Dim strInputfile As String
Dim dlgOpen As FileDialog
Dim bob As String
bob = Application.CurrentDb.Name
' select connection
Set dlgOpen = Application.FileDialog(msoFileDialogOpen)
With dlgOpen
.AllowMultiSelect = False
.Show
End With
strInputfile = dlgOpen.SelectedItems.Item(1)
' make connection
Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection
Dim strcnn As String
strcnn = "Provider=Microsoft.jet.OLEDB.4.0; Data Source=" & strInputfile
cnn.Open strcnn
' MsgBox "connection made"
' create recordset
Dim rec As ADODB.Recordset
Set rec = New ADODB.Recordset
rec.Open "SELECT * FROM SATransfers;", cnn
Dim n As Long
Dim i As Long
Do While Not rec.EOF
Debug.Print rec.Fields(0).Value; rec.Fields(1).Value; rec.Fields(2).Value; rec.Fields(3).Value
rec.MoveNext
Loop
' create table from connection cnn
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Dim rs As New ADODB.Recordset
cmd.ActiveConnection = cnn
cmd.CommandText = "SELECT SATransfers * INTO tmp1 FROM SATransfers;"
Set rs = cmd.Execute
cnn.Close
Set cnn = Nothing
Set cmd = Nothing
End Sub
View 2 Replies
View Related
Oct 12, 2012
I have a table orders details with a sub form displaying the order, I have a combo box to display the product from products table to place in the sub form products box . I keep getting the error message join key of table details not in record set I have checked my relationships and for keys and there all there .
View 1 Replies
View Related
Jun 11, 2013
I have a main form with combo boxes to filter a subform. I want to be able to export the filtered subform data to a table within the database and only include the filtered dataset not all records.
View 14 Replies
View Related
Jan 30, 2006
Hi All,
A bit new to this, any help most appreciated.
I'm trying to set up a table which will have multiple fields (a recordset? is that right, if so I don't know how to set one up in access). Its for a skill set which is utilised by specific lines in a production plant.
Each line in the plant has a set of skills required. I have a skills table (SkillID and Skill), what I want to do is have a Skillset which I can then link to each line
So for example Skillset 1 with a SkillsetID will also have in that table skill 1, skill 2, skill 3, but all taken from the Skill table. However that would involve having the SkillID field numerous times in the same table, but this cannot be done?
The idea is that for each line I can link the Line table to the Skillset table and that tells you what skills are needed for that line.
I hope this makes sense.
Thanks all.
View 2 Replies
View Related
Nov 25, 2014
Basically, I want to be able to click on the New (blank) record button, and then start adding in my Attraction, Date, etc fields in the main form, however I keep coming up with the error:
Cannot add record(s); join key of table 'OrdersList' is not in recordset.
I've gone back and checked all my relationships, and the query the form is based on, and all appears to be working there fine. I can manually enter information on to the tables & queries just fine, (but obviously I don't want other users to be able to see these).
I'm wondering if it's something to do with the Auto Number, which is also my PK in table 'OrdersList'. As the first design of this database had this field set to a Text field and I would manually enter the next sequence and I didn't have any issues adding new Orders to the form.
The form is 'Orders', which is based on a query called 'Orders List'.
View 12 Replies
View Related
Jan 20, 2015
I have inherited an old database with a new field to add some new invoice numbers and increment the number to each recordset by + 1. Starting at 001. I have formatted the field to a Number Field and formatted it to 000"/2015". I now need to populate over 2000 records with the sequential numbers for each record.
I have created the following code as my attempt. Of course it doesn't work!
Code:
Sub AddNumbertoRecords()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim counter As Long
counter = 1
Set db = CurrentDb()
[Code] ....
View 7 Replies
View Related
Oct 15, 2014
I have a normal Access table. The first column is ID and is the primary key. The second column is a text. The table consists of two records and is sorted in ascending order. First comes the data set with the ID 1.
Sub test()
Set rs_access = CurrentDb.OpenRecordset("tab1")
rs_access.MoveFirst
MsgBox (rs_access.Fields("id").Value)
End Sub
The message box shows 1. So everything is fine. Now I sort the table descending. But I get the same message. Why? Appearently the recordset doesn't know that the table was modified. The recordset should register that the table is modified.
View 1 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
Apr 14, 2015
I have a form that gets its info from a query, I would like to be able to add a new item, Customer, etc, etc. But, when I try to add one (I have a button using VBA code, ill post that at the end) it gives me the error
Code:
Cannot add record(s); join key or table tblitems not in recordset.
Code:
Private Sub cmdEventNewI_Click()
Me.Visible = False
DoCmd.OpenForm "frmItemsEdit1", acNormal, , , acFormAdd, acDialog
Me.Visible = True
Me.lstItems.Requery
End Sub ' cmdEventNewI_Click
View 8 Replies
View Related
Jan 26, 2015
I am fairly new to access, and have the following problem with the attached new DB. Thought I have designed the start of a good database with a "Junction Table". Have 1 problem, cannot enter a new name on the from, gives the error below:
"Join key of personnel table not in recordset" ...
View 2 Replies
View Related
