Combo boxes into temporary table??

[KrebsATM02AG]

I have a form that has a couple of combo boxes and text boxes. When the user clicks on the "Add Fund" command button I want the information currently in the boxes to be sent to a temporary table. Can someone tell me how to do this??

Would I just make a normal table and have and sql statement insert and then when i'm done delete? If so what would the statement look like. Thanks -Doug

 

[redapples]

An update query might do it if thats what you want either use a query or cod ein the SQL string "INSERT INTO..."

to code it using ADO

dim cnn as ADODB.Connection
Dim rs as New ADODB.Recordset

set cnn = CurrentProject.Connection
rs.Open "TEMPtable",cnn,3,3

with rs
    .addnew
    !Field1 = txtbox
    !Field2 = cmbobox
    '&c...
    .update
    .close
End With

set rs = nothing
set cnn = nothing

'DAO version

Dim db as DAO.Database
Dim rs as DOA.Recordset

Set db = CurrentDB
set rs = db.OpenRecordset("TEMPtable")
with rs
    .addnew
    !Field1 = txtbox
    !Field2 = cmbobox
    '&c...
    .update
    .close
End With

set rs = nothing
set cnn = nothing

To delete either store a query or code a SQL string
e.g. DELETE TEMPtable.* FROM TEMPtable;

this is fine if you want to delete all the information if not then similar to above but once you have the correct record use rs.delete to delete the current record.

 

[KrebsATM02AG]

Could you show me the correct format of SQL in code?? Does it look something like this?? I can't find just a simple example and I'm trying to piece something together. Can you tell me what is wrong? It is giving me an "Expected: End of Statment" error. Maybe i'm not using the correct syntax to retrieve the information from the combo boxes??

Private Sub cmdAddRecord_Click()
Dim strSQL As String

strSQL = "SELECT [Forms!fAddNewAllocation!Account_Id], [Forms!fAddNewAllocation!Family_Name]," _
[Forms!fAddNewAllocation!Ticker] '[Forms!fAddNewAllocation!Fund_Name], _
[Forms!fAddNewAllocation!Allocation_Pct]INTO tblTemporaryAllocation _
FROM Forms!fNewAddAllocation;"

End Sub

 

[redapples]

the best way to learn the syntax is build a query using the query builder in access and view the SQL syntax there.

for what you want here is a sample from a DB of mine.

    strSql = "INSERT INTO tblRefsINQuarter ( Description, [Count] )  SELECT tblCodeContactReferral.Descri" _
              & "ption, Count(tblIndBioAcc.Key) AS CountOfKey FROM tblIndBioAcc INNER JOIN tblCodeContactRef" _
              & "erral ON tblIndBioAcc.[Referral from/contact type] = tblCodeContactReferral.Code WHERE (((t" _
              & "blIndBioAcc.[Contact/Rereferral date]) Between #" & FirstDate & "# And #" & SecondDate _
              & "#)) GROUP BY tblCodeContactReferral.Description;"

    DoCmd.RunSQL (strSql)
    DoCmd.RunSQL ("DELETE tblRefsOUTQuarter.* FROM tblRefsOUTQuarter;")

so the syntax you are after is INSERT INTO tablename ([field],[field])) SELECT [field],[field] AS (if renameing a field) FROM tablename & Joins WHERE list conditions GROUP BY list groupings

I have never tried to do what you are attempting i.e. assigning values from a form instead of tables. Is your form bound to a table?

If so try selecting fields from the bound table where the condition is the unique identifier of the record you want to store. NB in my example note the new line " _" is followed by the concatination symbol "&". Also notice I have used fields from my form in the following section

 "Between #" & FirstDate & "# And #" & SecondDate "#))"

I hope this has helped.

 

[KrebsATM02AG]

Thanks for you help, It made me understand what I am trying to do better.

Is there a way to use SQL to retrieve data from a form that is not bound to a table? Maybe I should bind it to the temporary table?

Maybe I'm not approaching this correctly?

The forms purpose is to input funds and money through combo boxes and text boxes. When onen fund is entered the "Add Fund" cmd button should add the fund to a temporary table and also display the fund in a list box below the combo boxes. Then you enter the next fund, etc... When you are done entering the funds the cmd button "save and exit" should check the total to make sure it is correct and if correct take the records from the temporary table and add it to the corresponding tables in the database.

Is this the correct way to acheive this??? Anys suggestions would be appreciated...I'm getting frustraded

 

[redapples]

I would use the DAO or ADO code I gave you in the first post. (a reminder with some addittions)
What this will allow you to do is add a new record for each fund. If you then set the List box Row Source to the temporary table and requery the control after each addition you could then have that keep up to date.

to code it using ADO


dim cnn as ADODB.Connection
Dim rs as New ADODB.Recordset

set cnn = CurrentProject.Connection
rs.Open "TEMPtable",cnn,3,3

with rs
    .addnew
    !ID = Account_Id
    !LastName = Family_Name
    '&c...
    .update
    .close
End With
lstFunds.Requery
set rs = nothing
set cnn = nothing

'DAO version

Dim db as DAO.Database
Dim rs as DOA.Recordset

Set db = CurrentDB
set rs = db.OpenRecordset("TEMPtable")
with rs
    .addnew
    !ID = Account_Id
    !LastName = Family_Name
    '&c...
    .update
    .close
End With
lstFunds.Requery
set rs = nothing
set cnn = nothing

For the Save and exit I think a query or SQL statement would work better.

Use an update Query as discussed above to move the data from the temporary table then a delete query to remove the information form the temporary table to clean it out for the next session.

You could have one routine as a Function sent for two buttons one to save & exit and one to just exit. These buttons could send a boolean value and if it is true then save if false then exit. e.g.

Function ExitApp(bSave)
    If bSave then
       Docmd.OpenQuery ("qryUpdate")'only saves if requested
    End if
    docmd.OpenQuery ("qryDeleteFromTemp")'always deletes
End Fuction

Private Sub btnSaveExit_Click
    Dim bSave as Boolean
    bSave = True
    Call ExitApp(bSave)
    Docmd.Close
End Sub

Private Sub btnExitOnly_Click
    Dim bSave as Boolean
    bSave = False
    Call ExitApp(bSave)
    Docmd.Close
End Sub

When using either DAO or ADO be sure to activate the reference (VBA window menuitem Tools|References).

It is well worth getting to grips with the DAO or ADO examples in any case as these have tons of uses. Unfortunately there is a poor selection of ADO help files in Access VBA but it is newer and set to replace DAO ( once M$ finish it ), although there are bit of functionality in DAO not yet available in ADO. Help can be found on the Web in the MSDN files. Or here in Tek-Tips of course.

hope this helps sort out your thought processes.
redapples