Capturing data from forms to tables.

[Lopi]

Hello all, I have just joined this forum in hopes of receiving some MS Access help. I have a form that when opened displays data retrieved from a query. Also on this same form, I have some text boxes for users select from a drop down and/or enter text. I am wanting to take the record displayed on the form from the query and the information enter by the user and create a record on a separte table?

[JConsulting]

Hello all, I have just joined this forum in hopes of receiving some MS Access help. I have a form that when opened displays data retrieved from a query. Also on this same form, I have some text boxes for users select from a drop down and/or enter text. I am wanting to take the record displayed on the form from the query and the information enter by the user and create a record on a separte table?

This is fairly involved...do you feel comfortable using VBA?

You need to outline a process as well answering these questions.

1) How will I initiate the write to the other table
2) What happens if the user changes his mind?
3) how can I change those extra fields if I want to once they get written?
4) what if the information entered is incomplete?
5) will the other table have all the columns necessary to accept the form's fields and data types?
6) how will I let my user know when the write is complete?
7) who will maintain the other table down the road if there is no form interface for it?
8) will I want a way to view the record in the other table if I click on the current record I'm on?

Just a few things to consider. :)
Otherwise, the coding part is fairly straight forward using a For Each Control in Me.Controls loop.

Let us know when you're ready.
J

[Lopi]

This is fairly involved...do you feel comfortable using VBA?

You need to outline a process as well answering these questions.

1) How will I initiate the write to the other table
2) What happens if the user changes his mind?
3) how can I change those extra fields if I want to once they get written?
4) what if the information entered is incomplete?
5) will the other table have all the columns necessary to accept the form's fields and data types?
6) how will I let my user know when the write is complete?
7) who will maintain the other table down the road if there is no form interface for it?
8) will I want a way to view the record in the other table if I click on the current record I'm on?

Just a few things to consider. :)
Otherwise, the coding part is fairly straight forward using a For Each Control in Me.Controls loop.

Let us know when you're ready.
J

I am not an expert with VBA, but I am comfortable with it and have used it quite a bit in the database referenced. Most of the VBA I have done, I have needed a little code to get me started in the right direction. Is it possible you could give me an exemple or would you need to know more about the form and table? ANy help is greatly appreciated. In the mean time I will do some research on the For Each Control in Me.Controls loop.

Thanks so much for your help.

[JConsulting]

I am not an expert with VBA, but I am comfortable with it and have used it quite a bit in the database referenced. Most of the VBA I have done, I have needed a little code to get me started in the right direction. Is it possible you could give me an exemple or would you need to know more about the form and table? ANy help is greatly appreciated. In the mean time I will do some research on the For Each Control in Me.Controls loop.

Thanks so much for your help.

This is a function I had laying around that basically harvests all the data from a form. It uses recordsets and the loop I spoke of. Take a look at it, and let me know if there are parts that you don't understand, and I'll try to help you through it.

Function UpdateTables(strSubForm As String)
' JT 09/29/2006 This function is used by all the Add forms. Data is collected from the forms and written to the appropriate tables.
    On Error GoTo UpdateTables_Error
    Dim ws As Workspace
    Set ws = DBEngine(0)
 
    Dim strUser As String
    Dim rs As DAO.Recordset
    Dim f As Form
    Dim c As Control
    Dim i As Long
    Dim strSQL As String
    Dim strFormField
    Dim lnID As Long
    Dim strMax As String
    Dim strMain As String
    Dim strIDfield As String
    Dim strTable As String
    'JT Collect Spec entry data for the specified form
    strSQL = "select * from tbl_Specs where tbl_AddForm = '" & strSubForm & "';"
    Set rs = CurrentDb.OpenRecordset(strSQL)
        strMain = rs.Fields("tbl_MainForm").Value
        strIDfield = rs.Fields("tbl_IDField").Value
        strTable = rs.Fields("tbl_Name").Value
        rs.Close
    Set rs = Nothing
    'JT Identify the form/subform you're working with
    Set f = Forms(strMain)(strSubForm).Form
    strSQL = "Select * from " & strTable & ";"
    'JT open the target table
    'ws.BeginTrans
    Set rs = CurrentDb.OpenRecordset(strSQL)
        rs.AddNew
        For i = 0 To rs.Fields.Count - 1
            For Each c In f
                strFormField = c.Name
                If rs.Fields(i).Name = strFormField Then
                    If InStr(strFormField, "Loc") And f(strFormField).Value = 0 Then
                        MsgBox "Location cannot be 0. Record not saved"
                        f(strFormField).SetFocus
                        Exit Function
                    End If
                    rs.Fields(i).Value = Nz(f(strFormField).Value)
                    Exit For
                End If
            Next c
        Next i
        rs.Update
    rs.Close
    Set rs = Nothing
end function
 

J