Form Reads from one table Writes to another table (1 Viewer)

[Bee]

Hi,

I wonder if anyone can help with this please. I don't know if it's possible in Access:

I want to create a form that Reads from one table, but Writes to a different table.

Any examples, suggestions will be very much appreciated.

Thanks,
B

 

[Rabbie]

Its possible but we would need more detail to advise you further

 

[Uncle Gizmo]

Assuming your form is displaying information from one table in text boxes, then you can "write" this information to another table using ADO or DAO.

I think you need to give some thought to whether you want to add a record or update a record, or do both.

 

[Uncle Gizmo]

I am currently working on a system to save default values to a table.

You should be able to adapt the code below:

The decision you have to make is whether the information you have is an update or a new record. I solved this problem by adding everything as a new record, if the record already existed it threw an error and then the code would run an update instead. A better method would be to test to see if the record already existed, if it does exist update it, if it doesn't exist add a new record. Here's the code:

Function fSetDefault(strDfault As String, strCurVal As String)
'From: http://support.microsoft.com/default.aspx?scid=kb;EN-US;202117

'This function sets the default value (a string) in the table "tblDefault" it requires three
'pieces of information to operate, the current windows username which it finds With the code below.
'the name of the default which is passed through the variable "strDfault" and the current
'value of that default,which is passed through the variable "strCurVal" the value of
'the default is saved in the table "tblDfault"
'Occasionally the table does not have a default entry listed. this code does not react,
'it does nothing, it does not throw an error or anything!
'Hence the need for the function "fInitaliseDfault" below.
'This function initializes the table, adding an entry if none exists, and throwing an error
'if creating a duplicate is attempted. This error is trapped and passed back to
'the function "fInitaliseDfault" as a boolean.

   On Error GoTo Err_ErrorHandler
   
   If fInitaliseDfault(strDfault, strCurVal) Then Exit Function 'Exit if creating a duplicate is attempted
   
Dim strUserName As String
'
'strUserName = fGetWinUserName() 'Your windows login name
strUserName = fUserGet
strUserName = "'" & strUserName & "'"

Dim strDfaultText As String
strDfaultText = "'" & strDfault & "'"

Dim strCurText As String
strCurText = "'" & strCurVal & "'"
   
    Dim adoCon As ADODB.Connection
    Dim adoCmd As ADODB.Command

   Set adoCon = CurrentProject.Connection
   Set adoCmd = New ADODB.Command

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String
Dim strSQL4 As String
Dim strSQL5 As String

strSQL1 = "UPDATE tblDefaults SET tblDefaults.DfaultVal = "
strSQL2 = "WHERE (((tblDefaults.DfaultUser)="
strSQL3 = ") AND ((tblDefaults.DfaultFor)="
strSQL4 = "));"

strSQL = strSQL1 & strCurText & strSQL2 & strUserName & strSQL3 & strDfaultText & strSQL4

   With adoCmd
        .ActiveConnection = adoCon
        .CommandType = adCmdText
        .CommandText = strSQL
        .Execute
   End With

Exit_ErrorHandler:
        adoCon.Close
    Set adoCon = Nothing
    Set adoCmd = Nothing
    
    Exit Function 'Sub Property

Err_ErrorHandler:
        Select Case Err.Number
            Case 1 'Not sure if there is an error code (1) I have never seen it yet
                MsgBox "produced by error code (1) please check your code ! Error Number >>>  " _
                & Err.Number & "  Error Desc >>  " & Err.Description, , conAppName
            Case Else
                MsgBox "Error From --- fGetDefault --- Error Number >>>  " & Err.Number _
                & "  <<< Error Description >>  " & Err.Description, , conAppName
        End Select
    Resume Exit_ErrorHandler
End Function      'fSetDefault()

Function fInitaliseDfault(strDfault As String, strCurVal As String) As Boolean
'This function was necessary because the two functions above do not throw an error if the table does not
'have an entry for the particular default! This function is designed to throw an error, it attempts
'to add a new record, if it is successful the new record is added and it returns "true ".
'If it is unsuccessful an error code -2147467259 is generated which is used to generate the boolean "false "
On Error GoTo Err_ErrorHandler

Dim strUserName As String
'strUserName = fGetWinUserName() 'Your windows login name
strUserName = fUserGet
strUserName = "'" & strUserName & "'"

Dim strDfaultText As String
strDfaultText = "'" & strDfault & "'"

Dim strCurText As String
strCurText = "'" & strCurVal & "'"

Dim strSQL As String
Dim strSQL1 As String
Dim strSQL2 As String
Dim strSQL3 As String

strSQL1 = "INSERT INTO tblDefaults(DfaultUser, DfaultFor, DfaultVal) VALUES ("
strSQL2 = ", "
strSQL3 = " )"

strSQL = strSQL1 & strUserName & strSQL2 & strDfaultText & strSQL2 & strCurText & strSQL3

Dim adoCon As ADODB.Connection
Dim adoCmd As ADODB.Command

   Set adoCon = CurrentProject.Connection
   Set adoCmd = New ADODB.Command

'Use a Command Object to issue an SQL statement
        With adoCmd
            .ActiveConnection = adoCon
            .CommandType = adCmdText
            .CommandText = strSQL
            .Execute
        End With

fInitaliseDfault = True

Exit_ErrorHandler:
    adoCon.Close
    Set adoCon = Nothing
    Set adoCmd = Nothing
    Exit Function

Err_ErrorHandler:

        Select Case Err.Number
            Case -2147467259  'An expected error --- duplicate values
            'MsgBox "An expected error --- duplicate values", , "Error Number >>>  " & Err.Number & _
            "  Error Desc >>  " & Err.Description, , conAppName
            fInitaliseDfault = False 'Default Already initialized
            
            Case Else
                MsgBox "Error From --- fInitaliseDfault() --- Error Number >>>  " _
                & Err.Number & "  <<< Error Description >>  " & Err.Description, , conAppName
        End Select
    Resume Exit_ErrorHandler
End Function      ' fInitaliseDfault()

 

[Bee]

I need to save new record into the Write table and here is a quick further explanation:

I basically have a DAT table that I use to look up information for a combobox. When the user selects any item of the combobox, the rest of the fields of that record are defaulted into a form. However, sometime the user may need to change some of the default information and save it. When that happens, I don't want the user to change the original default info in the DAT table. That's why I thought Reading from one table and Writing into another one might be appropriate for this.

Any thoughts please?
B

 

[neileg]

You could include all the fields from the Read table you want in the query that populates your combo box. Set the column widths to zero so the user doesn't see them. In the After Update event of the combo, use some code to set the values of your other controls that are bound to the Write table to those in the hidden combo columns. The user will still be able to alter these values if they wish.

 

[Bee]

I don't quite understand how the users will be able to alter the values! Do you mean the values will be altered after they are transefered into the Write table?

 

[neileg]

The controls on the form will be populated with the values. If these controls are bound to the Write table, a Save operation will put them to the underlying table. However until and unless the user updates the combo box entry, the values in thes controls can be altered at will. It's not a question of how, it's simply that there's nothing to stop the user changing these values. Only the combo box is bound to the Read table so there's no changing the data there.

See the attached example.

 

[Bee]

The controls on the form will be populated with the values. If these controls are bound to the Write table, a Save operation will put them to the underlying table. However until and unless the user updates the combo box entry, the values in thes controls can be altered at will. It's not a question of how, it's simply that there's nothing to stop the user changing these values. Only the combo box is bound to the Read table so there's no changing the data there.

See the attached example.

I could not receive the zip file. I wonder if you can remove its extension and resend it please. (Blocked by firewall)

 

[neileg]

Try this one.

 

[Bee]

That's a clever way of doing it - thanks for the example.