acNewRecord Custom MsgBox

[BEES via AccessMonster.com]

Hi,
I really need help with this.
I have two Table PatientRecordTb and PTRTransTb.
PTRTransTb is unbound Both Table are same .

Up on Add Record Cmd which is placed on PatientTransFm, It does not
Process correctly, Up to update SQL statement it work great

Massage Box also appears but Yes and No response is not working in both
acmAddRecord ‘’ as well as Delete.

It will be great if Message Box conformed the Number of records added and
delete in their message box.

Here is the Code I am using

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click
Dim pid As Long
Dim mySQLString As String



mySQLString = "INSERT INTO PatientRecordTb ( LastName, FirstName, MiddleName,
DOB, Address, City, ZipCode, Phone1, Phone2, Mobile, EntryDate, AgencyID,
StatusID, SubAgency, [Note], MdName, MdPhone, MdFax, MdAddress, NoVisit1,
Frequency1,TVisit1, NoVisit2, Frequency2,TVisit2, NoVisit3, Frequency3,
TVisit3,TotalNoOfVisitMade )"
mySQLString = mySQLString & " SELECT PTRTransTb.LastName, PTRTransTb.
FirstName, PTRTransTb.MiddleName, PTRTransTb.DOB, PTRTransTb.Address,
PTRTransTb.City, PTRTransTb.ZipCode, PTRTransTb.Phone1, PTRTransTb.Phone2,
PTRTransTb.Mobile, PTRTransTb.EntryDate, PTRTransTb.AgencyID, PTRTransTb.
StatusID, PTRTransTb.SubAgency, PTRTransTb.Note, PTRTransTb.MdName,
PTRTransTb.MdPhone, PTRTransTb.MdFax, PTRTransTb.MdAddress, PTRTransTb.
NoVisit1, PTRTransTb.Frequency1, PTRTransTb.TVisit1, PTRTransTb.NoVisit2,
PTRTransTb.Frequency2, PTRTransTb.TVisit2, PTRTransTb.NoVisit3, PTRTransTb.
Frequency3,PTRTransTb.TVisit3,PTRtransTb.TotalNoOfVisitMade"
mySQLString = mySQLString & " FROM PTRTransTb;"

DoCmd.RunSQL (mySQLString)

Up till here it is working good
If MsgBox( _
"Are you sure you want to Add these records?", _
vbYesNo, _
"Confirm acNewRec") _
= vbYes _
Then
DoCmd.SetWarnings False
DoCmd.GoToRecord , , acNewRec
End If

If MsgBox( _
"Are you sure you want to delete these records?", _
vbYesNo, _
"Confirm Deletion") _
= vbYes _
Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("delete from PTRTransTb;")
Me.Requery
DoCmd.SetWarnings True


Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.Description
Resume Exit_AddRecord_Click
End If

End Sub.

Up on delete Auto no should be Zero Table completely Empty .
I will be really appreciated If some one can help me whit this

 

[Yanick]

I'am not sure of want you are triying to do but here is what I think.

I think there is a logic error in your code. Like it is right now, you ask
if we really want to add a record after DoCmd.RunSQL (mySQLString).

Try to move that command in you if statement.

If MsgBox("Are you sure you want to Add these records?", _
vbYesNo, "Confirm acNewRec") = vbYes _
Then
DoCmd.SetWarnings False
DoCmd.RunSQL (mySQLString)
End If

Is this what you are trying to do?
--
Yanick

Hi,
I really need help with this.
I have two Table PatientRecordTb and PTRTransTb.
PTRTransTb is unbound Both Table are same .

Up on Add Record Cmd which is placed on PatientTransFm, It does not
Process correctly, Up to update SQL statement it work great

Massage Box also appears but Yes and No response is not working in both
acmAddRecord ‘’ as well as Delete.

It will be great if Message Box conformed the Number of records added and
delete in their message box.

Here is the Code I am using

Private Sub AddRecord_Click()
On Error GoTo Err_AddRecord_Click
Dim pid As Long
Dim mySQLString As String



mySQLString = "INSERT INTO PatientRecordTb ( LastName, FirstName, MiddleName,
DOB, Address, City, ZipCode, Phone1, Phone2, Mobile, EntryDate, AgencyID,
StatusID, SubAgency, [Note], MdName, MdPhone, MdFax, MdAddress, NoVisit1,
Frequency1,TVisit1, NoVisit2, Frequency2,TVisit2, NoVisit3, Frequency3,
TVisit3,TotalNoOfVisitMade )"
mySQLString = mySQLString & " SELECT PTRTransTb.LastName, PTRTransTb.
FirstName, PTRTransTb.MiddleName, PTRTransTb.DOB, PTRTransTb.Address,
PTRTransTb.City, PTRTransTb.ZipCode, PTRTransTb.Phone1, PTRTransTb.Phone2,
PTRTransTb.Mobile, PTRTransTb.EntryDate, PTRTransTb.AgencyID, PTRTransTb.
StatusID, PTRTransTb.SubAgency, PTRTransTb.Note, PTRTransTb.MdName,
PTRTransTb.MdPhone, PTRTransTb.MdFax, PTRTransTb.MdAddress, PTRTransTb.
NoVisit1, PTRTransTb.Frequency1, PTRTransTb.TVisit1, PTRTransTb.NoVisit2,
PTRTransTb.Frequency2, PTRTransTb.TVisit2, PTRTransTb.NoVisit3, PTRTransTb.
Frequency3,PTRTransTb.TVisit3,PTRtransTb.TotalNoOfVisitMade"
mySQLString = mySQLString & " FROM PTRTransTb;"

DoCmd.RunSQL (mySQLString)

Up till here it is working good
If MsgBox( _
"Are you sure you want to Add these records?", _
vbYesNo, _
"Confirm acNewRec") _
= vbYes _
Then
DoCmd.SetWarnings False
DoCmd.GoToRecord , , acNewRec
End If

If MsgBox( _
"Are you sure you want to delete these records?", _
vbYesNo, _
"Confirm Deletion") _
= vbYes _
Then
DoCmd.SetWarnings False
DoCmd.RunSQL ("delete from PTRTransTb;")
Me.Requery
DoCmd.SetWarnings True


Exit_AddRecord_Click:
Exit Sub

Err_AddRecord_Click:
MsgBox Err.Description
Resume Exit_AddRecord_Click
End If

End Sub.

Up on delete Auto no should be Zero Table completely Empty .
I will be really appreciated If some one can help me whit this

 

[BEES via AccessMonster.com]

Thanks for your response. I should give a little background. What I am trying
to do is when Add Record Button is clicked It should give me Two options

1 " Are you sure you want to Add these records?"
If Yes Then It will Add these Records in ActualTable [
PatientRecordTb ]
If No Then It will remain in Transactional Tb.

2 " Are you sure you want to delete these records? "
If yes then it will delete these records from Transactional Table
AutoNumber Zero.
If No Then It will remain in Transactional Tb.

I tried what you recommended But On Add Record Message Box "Yes" It does not
transfer
records to PatientRecordTb. And Delete Record It Does not Delete Record.

But If I Repeat this command again it add 2 record and then delete from
table. Auto No continue
It should be zero after delete .

I really new with VB. I will be appreciated if you can tell me where to place
the code.

thanks for your help.

I'am not sure of want you are triying to do but here is what I think.

I think there is a logic error in your code. Like it is right now, you ask
if we really want to add a record after DoCmd.RunSQL (mySQLString).

Try to move that command in you if statement.

If MsgBox("Are you sure you want to Add these records?", _
vbYesNo, "Confirm acNewRec") = vbYes _
Then
DoCmd.SetWarnings False
DoCmd.RunSQL (mySQLString)
End If

Is this what you are trying to do?

Hi,
I really need help with this.

[quoted text clipped - 72 lines]

Up on delete Auto no should be Zero Table completely Empty .
I will be really appreciated If some one can help me whit this

 

[BEES via AccessMonster.com]

Thanks for your response. I should give a little background. What I am trying
to do is when Add Record Button is clicked It should give me Two options

1 " Are you sure you want to Add these records?"
If Yes Then It will Add these 5 Records in ActualTable [
PatientRecordTb ]
If No Then It will remain in Transactional Tb.

2 " Are you sure you want to delete these 5 records? "
If yes then it will delete these records from Transactional Table
AutoNumber Zero.
If No Then It will remain in Transactional Tb.

I tried what you recommended But On Add Record Message Box "Yes" It does not
transfer
records to PatientRecordTb. And Delete Record It Does not Delete Record.

But If I Repeat this command again it add 2 record and then delete from
table. Auto No continue
It should be zero after delete .

I really new with VB. I will be appreciated if you can tell me where to place
the code.

thanks for your help.

I'am not sure of want you are triying to do but here is what I think.

[quoted text clipped - 16 lines]

 

[Yanick]

OK, in that case, have you try not removing the warnings? By default, access
will ask you those questions before adding or deleting any records.

Remove the lines with "DoCmd.SetWarnings"

Initiate de delete, it will be ask to confirm. Same for the save.

--
Yanick

Thanks for your response. I should give a little background. What I am trying
to do is when Add Record Button is clicked It should give me Two options

1 " Are you sure you want to Add these records?"
If Yes Then It will Add these Records in ActualTable [
PatientRecordTb ]
If No Then It will remain in Transactional Tb.

2 " Are you sure you want to delete these records? "
If yes then it will delete these records from Transactional Table
AutoNumber Zero.
If No Then It will remain in Transactional Tb.

I tried what you recommended But On Add Record Message Box "Yes" It does not
transfer
records to PatientRecordTb. And Delete Record It Does not Delete Record.

But If I Repeat this command again it add 2 record and then delete from
table. Auto No continue
It should be zero after delete .

I really new with VB. I will be appreciated if you can tell me where to place
the code.

thanks for your help.

I'am not sure of want you are triying to do but here is what I think.

I think there is a logic error in your code. Like it is right now, you ask
if we really want to add a record after DoCmd.RunSQL (mySQLString).

Try to move that command in you if statement.

If MsgBox("Are you sure you want to Add these records?", _
vbYesNo, "Confirm acNewRec") = vbYes _
Then
DoCmd.SetWarnings False
DoCmd.RunSQL (mySQLString)
End If

Is this what you are trying to do?

Hi,
I really need help with this.

[quoted text clipped - 72 lines]

Up on delete Auto no should be Zero Table completely Empty .
I will be really appreciated If some one can help me whit this

 

[BEES via AccessMonster.com]

I did remove DoCmd.SetWarnings Line
But still there is no change, It does not ask default Question,
I noticed that Default question appears with this :

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

2 MAIN PROBLEMS

1) Upon ADD RECORD cmd 1 st time, It does not transfer Data to the Main
Table
and if I press it again, then double records are added to the main Table.

2) The Mgs Box Yes And No response Is not Working properly.
[ Detail explaination in previous post , Plea , SOS ect......]

I don't know where I am wrong??

Once again thanks for helping me out .

OK, in that case, have you try not removing the warnings? By default, access
will ask you those questions before adding or deleting any records.

Remove the lines with "DoCmd.SetWarnings"

Initiate de delete, it will be ask to confirm. Same for the save.

Thanks for your response. I should give a little background. What I am trying
to do is when Add Record Button is clicked It should give me Two options

[quoted text clipped - 42 lines]