copy record in subform (2 Viewers)

[theinviter]

hi Guys
need help

i have a form and subform , when user click on button then record in form will paste in subform but have issue if the ID is null then paste immediately or if ID not null then go to new record.

i tried this code but dose not work

Private Sub Command1608_Click()
On Error GoTo new_Err

Me.[cycle subform].SetFocus 'sets the focus to sfrm_Each_Book_subform
If ID <> Null Then
DoCmd.RunCommand acCmdRecordsGoToNew

Me.[cycle subform].Form.Cycle1 = Me.No_of_Cycle.Value
Me.[cycle subform].Form.date_ = Date
MsgBox "Update succefully "
Else
Me.[cycle subform].Form.Cycle1 = Me.No_of_Cycle.Value
Me.[cycle subform].Form.date_ = Date
End If
new_Err:

End Sub

 

[theDBguy]

Hi. Before we help you with that, can you please elaborate on why you seem to be trying to duplicate data from one table into another? Just curious...

 

[pbaldy]

For starters you can't test for Null like that. Try

If Not IsNull(ID) Then

And exactly what does "dose not work" mean?

 

[theinviter]

Hi. Before we help you with that, can you please elaborate on why you seem to be trying to duplicate data from one table into another? Just curious...

FOR Record keeping to present the number in the report

 

[theinviter]

For starters you can't test for Null like that. Try

If Not IsNull(ID) Then

And exactly what does "dose not work" mean?

i tried but it dose not paste and remain in new record

Private Sub Command1608_Click()
On Error GoTo new_Err

Me.[cycle subform].SetFocus 'sets the focus to sfrm_Each_Book_subform
If Not IsNull(ID) Then
DoCmd.RunCommand acCmdRecordsGoToNew

Me.[cycle subform].Form.Cycle1 = Me.No_of_Cycle.Value
Me.[cycle subform].Form.date_ = Date
MsgBox "Update succefully "
Else
Me.[cycle subform].Form.Cycle1 = Me.No_of_Cycle.Value
Me.[cycle subform].Form.date_ = Date
End If
new_Err:

 

[theDBguy]

FOR Record keeping to present the number in the report

Hi. Not sure that's clear enough. If you store some information in a table, doesn't that mean it is being maintained for record keeping? Or, are you talking about a historical archive?

 

[pbaldy]

I thought you had this working:

https://www.access-programmers.co.uk/forums/showthread.php?t=307387

 

[theinviter]

its working if there previous record ( ID in subform not null) but if ID is null then id dose not paste in same row.

 

[arnelgp]

please clarify what do you mean by Null? No record on subform?
how about there is "ID"? there is same ID on current highlight on subform?

 

[theinviter]

please clarify what do you mean by Null? No record on subform?
how about there is "ID"? there is same ID on current highlight on subform?

please find attached picture

 

[arnelgp]

either ID is null or not you will add the record?

Private Sub Command1608_Click()
On Error GoTo new_Err

	With Me![cycle subform].Form.RecordsetClone
		.AddNew
		!Cycle1 = Me.No_of_Cycle.Value
		!date_ = Date
		.Update
	End With
new_Err:
	Msgbox Err.Number & vbcrlf & Err.Description 
End Sub

 

[theinviter]

either ID is null or not you will add the record?

Private Sub Command1608_Click()
On Error GoTo new_Err

	With Me![cycle subform].Form.RecordsetClone
		.AddNew
		!Cycle1 = Me.No_of_Cycle.Value
		!date_ = Date
		.Update
	End With
new_Err:
	Msgbox Err.Number & vbcrlf & Err.Description 
End Sub

Dear sir;
this is working but the issue is that it automatically add new record but what if the current record is blank (null) wanna update it and if not go to new record


please se attached form for error i get

 

[arnelgp]

Private Sub Command1608_Click()
Dim bolWithBlank As Boolean
Dim bm As Variant
On Error GoTo new_Err

	With Me![cycle subform].Form.RecordsetClone
		If Not (.BOF And .EOF) Then
			.MoveFirst
			Do While Not .EOF
				If Len(Trim(!Cycle1.Value & "")) = 0 Then
					bolWithBlank = True
					bm = .Bookmark
					Exit Do
				End If
				.MoveNext
			Loop
			If bolWithBlank
				.Bookmark = bm
				.Edit
			Else
				.AddNew
			End If
			!Cycle1 = Me.No_of_Cycle.Value
			!date_ = Date
			.Update
		Else
				
			.AddNew
			!Cycle1 = Me.No_of_Cycle.Value
			!date_ = Date
			.Update
		End If
	End With
new_Err:
	Msgbox Err.Number & vbcrlf & Err.Description 
End Sub

 

[theinviter]

Private Sub Command1608_Click()
On Error GoTo new_Err

	With Me![cycle subform].Form.RecordsetClone
		If Not (.BOF And .EOF) Then
			.MoveFirst
			If Len(Trim(!Cycle1.Value & "")) > 0 Then
				.AddNew
			Else
				.Edit
			End If
			!Cycle1 = Me.No_of_Cycle.Value
			!date_ = Date
			.Update
		Else
				
			.AddNew
			!Cycle1 = Me.No_of_Cycle.Value
			!date_ = Date
			.Update
		End If
	End With
new_Err:
	Msgbox Err.Number & vbcrlf & Err.Description 
End Sub

i work for 1st record the i got error message ,
and it leave a blank row and add the data in second row.

please see the image

 

[theinviter]

my idea is that whenever the use click on the button then subform will be updated automatically with the value present in the form
for example if user click 10 time then the subform will be updated 10 time with value present in Nf_cycle.

 

[arnelgp]

I edited my last Code, please see it.

 

[theinviter]

I edited my last Code, please see it.

i used but same error i get

 

[arnelgp]

debug your code.
click on the Sub name and press F9.
run your form and click the button.
when it brings the code, press F8 until the error occur.
post the line where the error is.

also post the code you have so far.

 

[theinviter]

debug your code.
click on the Sub name and press F9.
run your form and click the button.
when it brings the code, press F8 until the error occur.
post the line where the error is.

also post the code you have so far.

please find the attached picture

 

[arnelgp]

try this, and don't use Me.[cycle subform], i am already using it's Recordset so no need to use the Form.
Copy and Replace your code.

Private Sub Command1608_Click()
Dim bolWithBlank As Boolean
Dim bm As Variant
On Error GoTo new_Err

	With Me![cycle subform].Form.RecordsetClone
		If Not (.BOF And .EOF) Then
			.MoveFirst
			Do While Not .EOF
				If Len(Trim(!Cycle.Value & "")) = 0 Then
					bolWithBlank = True
					bm = .Bookmark
					Exit Do
				End If
				.MoveNext
			Loop
			If bolWithBlank
				.Bookmark = bm
				.Edit
			Else
				.AddNew
			End If
			!Cycle = Me.No_of_Cycle.Value
			!date_ = Date
			.Update
		Else
				
			.AddNew
			!Cycle = Me.No_of_Cycle.Value
			!date_ = Date
			.Update
		End If
	End With
new_Err:
	Msgbox Err.Number & vbcrlf & Err.Description 
End Sub