Solved New Record Replacing Previous One in Unbound Subform (1 Viewer)

[Ashfaque]

Hi,

I have a unbound form and unbound sub-form in it. I can save my record in main form with vba codes no issue. The issue is with Unbound Subform.

I have placed on btn with below code on main form to get relevant dependents (family members of our employee) of the emp I select on main form. It is selecting also but problem is it is going to get second dependent, first dependents data replacing with first dependent record and appearing twice in subform. Saving of subform record shall be another question but not now...

Dim db As DAO.Database
Dim rst, DRst As DAO.Recordset
Dim Rec As Integer

Set rst = CurrentDb.OpenRecordset("Select * from T_Dependents where Cno = " & Me.CNo)
If rst.EOF And rst.BOF Then
MsgBox ("No Records In Found"), vbInformation, "Null Records Inf."
Exit Sub
Else
rst.MoveFirst
Rec = Rec + 1
Do Until rst.EOF = True

Set DRst = CurrentDb.OpenRecordset("Select * from T_TicketDetails")
DRst.AddNew

SF_Dependents!depenname = rst!depenname
SF_Dependents!DepenRelation = rst!DepenRelation
SF_Dependents!AllowedTicket = rst!AllowedTicket
SF_Dependents!VacNum = Forms!F_Vacations!VacNum
SF_Dependents!CNo = Forms!F_Vacations!CNo
SF_Dependents!ID = Rec
Rec = Rec + 1

rst.MoveNext

Loop
End If

Please help me. Thanks in Advance.

 

[arnelgp]

you can't do like that on unbound form.
use a Temp table and add all records there.
use the Temp table for your subform.

 

[Ashfaque]

You mean at every click of btn the relevant dependents record will be saved in Temp tbl and immediate those records will be pulled in my sub-form?

In that case I need to make my subform bound with real tbl? Correct?

 

[Ashfaque]

Thanks Arnel,

I tried following way you described. Half code is placing relevant dependents in temp tbl.

Dim strSQL, MystrSQL As String
Dim strTable As String
strTable = "tblTempTest"
StrMyTbl = "T_TicketDetails"
DoCmd.DeleteObject acTable, strTable ' Deleting temp tbl
strSQL = "Select * INTO " & strTable & " FROM T_Dependents " & _
"Where Cno = " & Me.CNo
CurrentDb.Execute strSQL

' ABOVE WORKS FINE
' NOW BELOW I M TRANSFERING RECORD TO
Dim db As DAO.Database
Dim rst, DRst As DAO.Recordset
Dim Rec As Integer

Set rst = CurrentDb.OpenRecordset("Select * from tblTempTest")
If rst.EOF And rst.BOF Then
MsgBox ("No Records In Found"), vbInformation, "Null Records Inf."
Exit Sub
Else

MystrSQL = "Select * INTO T_TicketDetails FROM tblTempTest " & _
"Where Cno = " & Me.CNo
CurrentDb.Execute MystrSQL - THE ERROR IS HERE
End If

 

[Ashfaque]

Better to make it thru Append Query.... I was planing to make it thru VBA. Anyways...

 

[Pat Hartman]

Is there some reason you are using unbound forms? Access is a RAD (Rapid Application Development) tool. Bound forms is its biggest feature. If you are not using the RAD features of Access, you really should be using some other development platform more to your liking.

 

[Ashfaque]

Thanks Pat,
I changed the idea of unbound sub-form. The main form shall be only unbound and sub-form will be bound. I applied append query and and saving data sub-form in separate table. It is working fine with me.

Thanks for your support.

 

[Pat Hartman]

You do realize that a bound form doesn't require the kind of code you've had to write?

 

[Ashfaque]

Yes, I wish if it could work. But when a simple append query works it out then lengthy piece of vba code doesn't require.

 

[Pat Hartman]

Bound forms require no code to be fully functional unless you want to validate the data being entered but then you would need to validate the data regardless of whether the form is bound or unbound.