I have designed a form from a query. I also have a few unbound inputs on the form. I am new to vba. I need to know how to add the information to the new table. The field names are different than the ones in the form.



Betsy

Betsy

I'm not understanding where the 'new table' fits in. The fundamental issues pertain to the structure and relationships between the data tables. Does the 'new table' have a one to one relationship with the table(s) in your query? Most likely it should if these inputs on the form are part of and unique to each record. If so, why not include the fields in the query and bind them to your controls?

If for whatever reason that is not possible, you need to decide which event to hook to fire the code to write the unbound inputs to the target table. You could add a button for the users to click to save the unbound portions of the multi table record, but that would be counter intuitive for users accustomed to bound controls. Your best bets are the form Before or After Update events. You still have to deal with the matter of how to fill the unbound parts of the form as you navigate the form records and when you need to add a new record to the 'new table' and when you need to modify and existing record in the new table. Once you decide how you want all this to happen and to code that, you can deal with the mechanics of the question you actually asked. At that point you have two options:
1. Open a recordset against the database table and use the .addNew/.Update methods referencing the field names and control values. You can choose a type of recordset that returns no records to keep performance up (in DAO, dynaset dbAppendOnly for example). Code would look something like:

Dim r as Recordset
Set r = CurrentDb.OpenRecordset("Select * From tblNew", set your parameters between the commas here)
r.Addnew
r!field1 = Me.txtControlName1
r!field2 = Me.cboControlName2
.
.
.
r.Update
r.Close
Set r = Nothing

If you are editing an existing record, you will need to add a where clause to the recordset to select the particular record that needs to be edited. The code is otherwise the same except the r.Addnew line is changed to r.Edit.

2. The other, and generally more efficient approach for a single record is to run an append or update query. There is a post in the past week responding to a topic about adding data to two tables that I wrote that illustrates some of the basics of that approach. The matter of distinguishing between a new or edited record is also raised there.

Another real issue with unbound data is that the programmer takes the responsibility and blame for any concurrency consequences. If you are asking about how to use code to add data to a table, then you should probably know that you should read the unbound data before user edits and check it again before updating the data to ensure no other user has edited any part of the record before his/her edit is allowed to be written. This is more work and traffic over the LAN (slower performance) than simply using bound fields.

These questions should not ordinarily arise in a database where the data structure is properly normalized.

Ciao
Jurgen Welz
Edmonton AB Canada
[email protected]

I need to put more detail into what I am doing. I am enclosing my code to show you what I am trying to do. I am trying to use the values from the form input to another table. The form is being filled in with information from a query which were read only. There are few other fields which are information which the user adds the information. I am using Access 97. I would appreciate any help in the right direction.

Private Sub Add_Rec_Click()
On Error GoTo Err_Add_Rec_Click
Dim strQueryTxt
strQueryTxt = "INSERT INTO invent(CreateDate, FirstArticleCheck,Customer,CustPart,CustPartRev,Pl xPart,PlxPartDescription,Requestor,InspectCriteria Notes,Rev,manname,manufacturerno,mitem,[stat grp],qcode )"

strQueryTxt = "values (" & Me.CreateDate & " AS Expr1, " & Me.TXT_Inspect & " AS Expr2," & Me.[Customer Name] & " AS Expr3," & Me.cpnplexusp_n & " AS Expr4," & Me.CustPartRev & " AS Expr5," & Me.[Item Number] & " AS Expr6, " & Me.[Item Desc] & " AS Expr7, '" & Me.Requestor & " AS Expr8, " & Me.InspectCriteriaNotes & " AS Expr9," & Me.Rev & " AS Expr10, " & Me.manufacturer_name & " AS Expr11, " & Me.manufacturer & " AS Expr12, " & Me.mitem & " AS Expr13, " & Me.StatGrp & " AS Expr14, " & Me.Q_code & " AS Expr15 &)"



    DoCmd.RunSQL strQueryTxt

Exit_Add_Rec_Click:
    Exit Sub

Err_Add_Rec_Click:
    MsgBox Err.Description
    Resume Exit_Add_Rec_Click

End Sub

Betsy

Open the visual basic editor and place a breakpoint on the line with the DoCmd statement. Then return to MS Access and try to add a record using your form. The application will stop running when the cursor comes to the breakpoint you just entered. Next, if you don't have an immediate window open, open one from the debug menu.

In the immediate window type ?strQueryTxt and then press enter. This will print the value of your variable strQueryTxt in the immediate window. If the code in your application is exactly as what you have posted, your variable will begin with "values (" ... If that is true, you are not passing your SQL Action command, in this case "INSERT INTO ..." to your RunSQL method.

The fix is rather simple. Change the beginning of the second strQueryTxt assignment as follows:

    

Hope this helps.



Darrell L. Embrey

A few suggestions:

1. Your Dim creates a variant when it would be more efficient as a String so change it to:

    Dim strQueryTxt As String

2. As suggested by Darrell Embrey, you need to concatenate the two parts of the SQL string as the current approach just overwrites it when it is set the second time. However, date fields require date delimiters (#) and text fields require string delimiters ('). If one assumes that CreateDate stores a Date/Time value, the SQL string should indicate so as in the second part of the string below. I have assumed, almost certainly incorrectly, that most of the fields store text data but that [Item Number] and the last four values are numeric with the example code below:

    strQueryTxt = "INSERT INTO invent(CreateDate, firstArticleCheck, Customer, CustPart, " & _
        "CustPartRev, PlxPart, PlxPartDescription, Requestor, InspectCriteriaNotes, " & _
        "Rev, manname, manufacturerno, mitem, [stat grp], qcode ) "

    strQueryTxt = strQueryTxt & "values (#" & Me.CreateDate & "#, '" & Me.TXT_Inspect & "', '" & _
        Me.[Customer Name] & "', '" & Me.cpnplexusp_n & "', '" & Me.CustPartRev & "'," & _
        Me.[Item Number] & ", '" & Me.[Item Desc] & "', '" & Me.Requestor & "', '" & _
        Me.InspectCriteriaNotes & "', '" & Me.Rev & "', "' & Me.manufacturer_name & "', " & _
        Me.manufacturer & ", " & Me.mitem & ", " & Me.StatGrp & ", " & Me.Q_code & ")"

4. As you can see I dropped the 'As Expr..' syntax as it is unnecessary. It is however necessary to ensure that you have a value for each field.

5. I don't like using the DoCmd.RunSQL method since it is essentially code calling macro where code itself is better, faster and allows you to check whether the record was added with the records affected property. To use it you could create a database variable, use its execute method and then check whether the write was successful.

    Dim db As Database ' or DAO.Database
    db.Execute strQueryTxt
    If Not(db.RecordsAffected) Then
        Msgbox "Record not written. Please verify range of values and ensure all required " & _
            "fields have been set."
    End If

Exit_Add_Rec_Click:
    Set db = Nothing
    Exit Sub

Err_Add_Rec_Click:
    MsgBox Err.Description
    Resume Exit_Add_Rec_Click

End Sub

Ciao
Jürgen Welz
Edmonton AB Canada
[email protected]

I was able to get this part of my application working. Thank you,Jürgen for your assistance. The code was just what I needed to make my application work.



Betsy

thank you!!!