How to OpenRecord, Edit & Save to different table In a Form 1

[manf01]

Hi All, This is my second post here, you helpers are doing a great job. We appreciate it. Thanks a lot.

I got a problem related to the forms now. I need to print a report, I this case I have
for example 2 tables( mainform and subform), one is self created and other one is a linked table(linked database) to get the given data. I want to create a form(for user) to create a report. I have two main problems here.

Data which is coming from linked table will be modified, but in this case it should not make any changes in linked table (only in the form). My choice is that this data which is edited and added in the form should be saved in a separate table. And printing the current data for reporting.

Thanks again, looking forward to a positive reply.

 

[ZmrAbdulla]

You need to have an unbound form to open /Edit & update the record to a new table.
here is an example. The code below opens a recordset and fill the text boxes of an unbound form.

Private Sub cboLastName1_AfterUpdate()
On Error Resume Next
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    cboLastName1.SetFocus
    If cboLastName1.Value > 0 Then
        strSQL = "SELECT * FROM tblTable WHERE ID = " & cboLastName1.Value
        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL)
        If Not rs.BOF Then
            Me.ID = rs("ID")
            Me.Prefix = rs("Prefix")
            Me.FirstName = rs("FirstName")
            Me.MiddleName = rs("MiddleName")
            Me.LastName = rs("LastName")
            Me.Suffix = rs("Suffix")
            Me.Nickname = rs("Nickname")
            Me.Title = rs("Title")
        End If
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
    End If
End Sub

Then add a command button on the form to update the record to the new table. The code below open a new recordset in the new table. You can edit the record on the form and it won't be affected to the original records as far as you are not updating to the old table. You need to set reference to DAO in the VBA window TOOLS > REFERENCES

Private Sub cmdAddToNewTable_Click()
On Error Resume Next
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset("tblTableNew")
      With rs
        .AddNew
        rs!ID = Me.ID.Value
        rs!Prefix = Me.Prefix.Value
        rs!FirstName = Me.FirstName.Value
        rs!MiddleName = Me.MiddleName.Value
        rs!Suffix = Me.Suffix.Value
        rs!Nickname = Me.Nickname.Value
        rs!Title = Me.Title.Value
        .Update
      End With
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
  End Sub

Hope this helps.
If you need any more help please ask.

Zameer Abdulla
Visit Me (New Look & style)

 

[ZmrAbdulla]

First code is taken from Candace-Tripp Combo Populate form db and second is modified by myself.

Zameer Abdulla
Visit Me (New Look & style)

 

[manf01]

Thanks a lot ZmrAbdulla,

I have just checked your reply, i will try it and will let you know.

 

[ZmrAbdulla]

You welcome

Zameer Abdulla
Visit Me (New Look & style)

 

[manf01]

First code is working properly, for the second code i have a Problem. Should i create a new table my self ? i have created one with same Fields as in my main table on the form but i am getting Just ID number in my new table not the other field values.

Any Idea ?

Thanks

 

[ZmrAbdulla]

You need to add the data to a new table isn't it? You should have a new table ready that has required fields.(Some time you may not required all the fields to be added to the new table.)
If your first table's ID field is an Autonumber then the new table's Id field should be number (Long Integar)not autonumber. Delete the new record added and change the field to number.
You can have another field to be an autonumber.

Zameer Abdulla
Visit Me (New Look & style)

 

[manf01]

This is code of both functions but i have a problem with second function

Private Sub cboProjectNr_AfterUpdate()
On Error Resume Next

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
cboProjectNr.SetFocus

If cboAPNummer.Value > 0 Then
strSQL = "SELECT * FROM [Orders] WHERE OrderID = " & cboProjectNr.Value
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)
If Not rs.BOF Then
Me.[Project Number] = rs("Project Number")
Me.OrderID = rs("OrderID")
Me.Order_Nr = rs("Order_Nr")
Me.Ref_Nr = rs("Ref_Nr")
Me.Offer_Nr = rs("Offer_Nr")

End If
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

End If

End Sub






Private Sub cmdAddToReportTable_Click()
On Error Resume Next
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("ReportTable")
With rs
.AddNew
Me.[Project Number] = rs("Project Number")
Me.OrderID = rs("OrderID")
Me.Order_Nr = rs("Order_Nr")
Me.Ref_Nr = rs("Ref_Nr")
Me.Offer_Nr = rs("Offer_Nr")
.Update
End With
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
End Sub



After selecting the records, whenever I click on cmdAddReportTable button, each time it just adds 0 in Project number and in OrderID ( I set already the OrderID to a number in the ReportTable) and other fields empty (even after selecting on the form)

I made any mistake in code ? I am not good in VBA. Do you have any hints ?

Thanks a lot.

 

[ZmrAbdulla]

There is a difference in the code. First we open the recordset from the table to the form and second form the form to table. It should be like below.

Private Sub cmdAddToReportTable_Click()
On Error Resume Next
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset("ReportTable")
      With rs
        .AddNew
        ![Project Number] = Me.[Project Number].Value
        !OrderID = Me.OrderID.Value
        !Order_Nr = Me.Order_Nr.Value
        !Ref_Nr = Me.Ref_Nr.Value
        !Offer_Nr = Me.Offer_Nr.Value
        .Update
      End With
        rs.Close
        Set rs = Nothing
        db.Close
        Set db = Nothing
  End Sub

Remember!! Your ReportTable should not have any of the field to be PrimaryKey. Except a new one that is not included in the recordset. You can't add the record more than once.


Zameer Abdulla
Visit Me (New Look & style)

 

[manf01]

Thanks a lot Zameer,

It was a great help. Now i will try to work on it to send the present recod for print a report.

 

[ZmrAbdulla]

Glad it helped

Zameer Abdulla
Visit Me (New Look & style)