acNewRecord Custom MsgBox

  • Thread starter BEES via AccessMonster.com
  • Start date
B

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
 
Y

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


BEES via AccessMonster.com said:
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
Click to expand...
 
B

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.
Click to expand...
[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
Click to expand...
Click to expand...
 
B

BEES via AccessMonster.com

BEES said:
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.
Click to expand...
[quoted text clipped - 16 lines]
Click to expand...
 
Y

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


BEES via AccessMonster.com said:
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.
Click to expand...
[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
Click to expand...
Click to expand...
Click to expand...
 
B

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 .
Yanick said:
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
Click to expand...
[quoted text clipped - 42 lines]
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Can't get proper response from MsgBox 4
Dialog in MsgBox 4
OpenRecordSet Error 3

Top