Windows 7 32bit 
Access 2016
I have created a form and a sub form in it. main form table name is grey_offer and sub form table name is grey_shade. Table named grey_offer has 16 column after this when i type the back id number in the id column the old data is show and i can edit it and save then the data gets updated.
Now the problem comes here that when i type back id number and press enter, then data is shown in the main form but the data of the related sub form is not shown and the sub form remains empty with only new current id.
How can i solve this problem? my coding for searching the data is as follows.....
Private Sub TXT0_AfterUpdate()
SQL = "SELECT*FROM GREY_OFFER WHERE ID = " & Me.TXT0
Set DB = CurrentDb
Set RST = DB.OpenRecordset(SQL)
If RST.RecordCount > 0 Then
For DATACOL = 0 To 15
Me("TXT" & DATACOL) = RST.Fields(DATACOL)
Next
End If
End Sub
and save button code is follow-
Option Compare Database
Private ASAVED As Boolean
Dim DB As DAO.Database
Dim RST As DAO.Recordset
Private Sub SAVE_Click()
Set DB = CurrentDb
Set RST = DB.OpenRecordset("GREY_OFFER")
If (IsNull(Me![txt2].Value)) Then
MsgBox ("PLEASE ENTER VALUE")
Exit Sub
End If
If Me.txt2 = "" Then
MsgBox ("PLEASE ENTER VALUE")
Exit Sub
End If
If Me.TXT0 = 1 Then GoTo ADDNEW
If DMax("ID", "GREY_SHADE") + 1 >= Me.TXT0 Then GoTo ADDNEW
RST.MoveFirst
Do Until RST.EOF
If RST.Fields(0) = Me.TXT0 Then
RST.EDIT
For DATACOL = 0 To 15
RST.Fields(DATACOL) = Me("TXT" & DATACOL)
Next DATACOL
Dim answer As Integer
answer = MsgBox("Do you Wish to UPDATED data?", vbQuestion + vbYesNo + vbDefaultButton1, "User Repsonse")
If answer = vbYes Then
RST.Update
End If
Me.Undo
DoCmd.RefreshRecord
Me.TXT0 = DMax("ID", "GREY_OFFER") + 1
Me.txt1.SetFocus
Exit Sub
End If
RST.MoveNext
Loop
ADDNEW:
If Me.TXT0 < 1 Then
MsgBox ("ID NUMBER NOT TO BE 0")
Me.Undo
Me.TXT0 = DMax("ID", "GREY_OFFER") + 1
Me.TXT0.SetFocus
Exit Sub
End If
If DCount("*", "GREY_SHADE", "ID = " & Me.ID) < 1 Then
MsgBox ("PLEASE ENTER SHADE")
Me!GREY_SHADE.Form.SHADE.SetFocus
SendKeys "+{TAB}"
Exit Sub
End If
ASAVED = True
MsgBox ("data save successfully")
DoCmd.RunCommand (acCmdRecordsGoToNew)
Me.TXT0 = DMax("ID", "GREY_OFFER") + 1
Me.TXT0.SetFocus
End Sub
Reply With Quote
Originally Posted by deepaksharma
