Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-03-2019, 04:06 PM   #1
DiogoCuba
On Error GoTo AWF *****:
 
DiogoCuba's Avatar
 
Join Date: Dec 2018
Location: Rio de Janeiro - Brazil
Posts: 83
Thanks: 62
Thanked 1 Time in 1 Post
DiogoCuba is on a distinguished road
Unhappy Resetting formīs controls return a couple of errors.

Hi,

I spent my entire day trying to sort this out but I am giving up and asking for help:

I have a small form that will load with all controls locked, except one that the user can enter information to search for a record. The code will change the RecordSource of the form to a string containing the SQL string and if the Dcount > 0 then it requery the form, else he will return a message "no record is found". In addition: I need to prevent the user to dirty the formīs control before a record is populated, I use the locked proprety as default and unlock/lock them ont he fly.

The problem is when the form is populated, dirtied and I run my ResetCode it raises the error raised is the 2105: "You canīt go to the specified record."

After I get th error 2105, then the cmdSearch button starts returning error 2107 every single time I click it: "The value you entered does not meet the validation rule defined for the field or control."

When I use the F8 on the breakpoint, I noticed the code goes from to this event before updating the record source with the SQL string.:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String
        If Not blnGood Then Cancel = True
End Sub
It seems that beforeupdate event is being called too early but I canīt find the solution.

Does anybody know what is going on?

I have a copy of the file attached and the problem is on the frmNewFeedback.

I appreciate all the help!
Attached Files
File Type: accdb SOC Mgr - Stripped_v2_CR.accdb (1.46 MB, 25 views)

__________________
Best regards,
Diogo Cuba

"Stay Hungry, Stay Foolish: you only live once, so make it count and go after what you want no matter what."
DiogoCuba is offline   Reply With Quote
Old 01-03-2019, 05:02 PM   #2
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Resetting formīs controls return a couple of errors.

Just saw this before signing off for the night
I've altered your form & believe its now working OK

I believe error 2105 was because you were trying to move to a new record in a locked form.

I've fixed that by instead closing and reopening the form and made several other changes to the cmdReset_Click code

I've not dealt with error 2107 as that hasn't occurred for me since fixing the other issue
If that still occurs for you then a more radical change may be needed to your form

I've added error handling to the cmdReset_Click procedure.
You need to add your own error handling to EVERY procedure in your database.
It doesn't have to be the same as mine. There are lots of different approaches

I've also fixed the Exit button code on that form and got rid of the embedded macro that didn't close the form
Attached Files
File Type: zip SOC Mgr - Stripped_v3_CR.zip (166.9 KB, 23 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by isladogs; 01-03-2019 at 05:42 PM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
DiogoCuba (01-05-2019)
Old 01-05-2019, 08:51 AM   #3
DiogoCuba
On Error GoTo AWF *****:
 
DiogoCuba's Avatar
 
Join Date: Dec 2018
Location: Rio de Janeiro - Brazil
Posts: 83
Thanks: 62
Thanked 1 Time in 1 Post
DiogoCuba is on a distinguished road
Re: Resetting formīs controls return a couple of errors.

Quote:
Originally Posted by isladogs View Post
Just saw this before signing off for the night
I've altered your form & believe its now working OK
...
Colin, I am travelling now and I will take a look at the file you uploaded when I get at home.

Thanks for the feedback and if you have a good tutorial on forms design I will appreciate if you could refer to me.

Regards,

__________________
Best regards,
Diogo Cuba

"Stay Hungry, Stay Foolish: you only live once, so make it count and go after what you want no matter what."
DiogoCuba is offline   Reply With Quote
Old 01-05-2019, 09:03 AM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Resetting formīs controls return a couple of errors.

There are some good tutorials on the MS website and elsewhere.
Steve Bishop's You Tube videos may be helpful.

One thing I meant to say about that form of yours.
I would change the unbound textbox txtFindCard to a combo to make it easier for users to select from available values
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-05-2019, 09:16 AM   #5
AccessBlaster
.
 
Join Date: May 2010
Posts: 916
Thanks: 20
Thanked 241 Times in 230 Posts
AccessBlaster will become famous soon enough AccessBlaster will become famous soon enough
Re: Resetting formīs controls return a couple of errors.

Quote:
Originally Posted by isladogs View Post
Just saw this before signing off for the night
You actually sign off? Tell the truth you get some sort of audio alert right....
AccessBlaster is online now   Reply With Quote
Old 01-05-2019, 09:40 AM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Resetting formīs controls return a couple of errors.

LOL.
I do spend far too long on this site ....
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-05-2019, 11:48 AM   #7
DiogoCuba
On Error GoTo AWF *****:
 
DiogoCuba's Avatar
 
Join Date: Dec 2018
Location: Rio de Janeiro - Brazil
Posts: 83
Thanks: 62
Thanked 1 Time in 1 Post
DiogoCuba is on a distinguished road
Re: Resetting formīs controls return a couple of errors.

Quote:
Originally Posted by isladogs View Post
There are some good tutorials on the MS website and elsewhere.
Steve Bishop's You Tube videos may be helpful.
But do you know if someone has ever done a “dos and don’ts” for forms?

The bishop’ YouTube series is saved on my reference notebook, that form was based on one of his tutorials.

__________________
Best regards,
Diogo Cuba

"Stay Hungry, Stay Foolish: you only live once, so make it count and go after what you want no matter what."
DiogoCuba is offline   Reply With Quote
Old 01-05-2019, 11:52 AM   #8
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Resetting formīs controls return a couple of errors.

Quote:
Originally Posted by DiogoCuba View Post
But do you know if someone has ever done a “dos and don’ts” for forms
I expect so. Someone else may have some good suggestions
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-10-2019, 10:15 AM   #9
DiogoCuba
On Error GoTo AWF *****:
 
DiogoCuba's Avatar
 
Join Date: Dec 2018
Location: Rio de Janeiro - Brazil
Posts: 83
Thanks: 62
Thanked 1 Time in 1 Post
DiogoCuba is on a distinguished road
Re: Resetting formīs controls return a couple of errors.

Quote:
Originally Posted by isladogs View Post
I would change the unbound textbox txtFindCard to a combo to make it easier for users to select from available values
I see what you mean, Colin. Indeed that would be faster to find matching numbers. I will look for tutorial on how to do it.

Thanks for the feedback!

EDIT: I found this tutorial from Allen Browne, is that what you mean?

http://allenbrowne.com/ser-03.html

EDIT 2: Actually this sounds much more like what you mean: http://allenbrowne.com/ser-32.html
__________________
Best regards,
Diogo Cuba

"Stay Hungry, Stay Foolish: you only live once, so make it count and go after what you want no matter what."

Last edited by DiogoCuba; 01-10-2019 at 10:45 AM.
DiogoCuba is offline   Reply With Quote
Old 01-10-2019, 10:48 AM   #10
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Resetting formīs controls return a couple of errors.

I'd already done it before I wrote that reply.
Have a look at the attached.
I've copied a slightly modified version of the Search button code to the combo after update event.
Check its working correctly.
If so & you are happy with it, delete the Search button (currently its disabled)
Attached Files
File Type: zip SOC Mgr - Stripped_v4_CR.zip (166.3 KB, 15 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 01-10-2019, 11:56 AM   #11
DiogoCuba
On Error GoTo AWF *****:
 
DiogoCuba's Avatar
 
Join Date: Dec 2018
Location: Rio de Janeiro - Brazil
Posts: 83
Thanks: 62
Thanked 1 Time in 1 Post
DiogoCuba is on a distinguished road
Re: Resetting formīs controls return a couple of errors.

Quote:
Originally Posted by isladogs View Post
I'd already done it before I wrote that reply.
Have a look at the attached.
I've copied a slightly modified version of the Search button code to the combo after update event.
Check its working correctly.
If so & you are happy with it, delete the Search button (currently its disabled)
Thanks a lot, Colin!

I have tested and the error 2107 is triggered on the second record I pick from the combo box. First one is fine, but the second brings this:

Code:
 Private Sub txtFindCard_AfterUpdate()
    
    If Not IsNull(Me.txtFindCard) And IsNumeric(Me.txtFindCard) = True Then
        Dim strSQL As String
            strSQL = "SELECT tbl1Cards.CardID, tbl1Cards.CardNumber, tbl1Cards.TeamSupervisorFK, tbl1Cards.FeedbackOn, tbl1Cards.StatusCardFK, tbl1Cards.ClosedOn, tbl1Cards.Description, tbl1Cards.DescriptionPor, tbl1Cards.FeedbackMessagePor, tbl1Cards.FeedbackMessage, tbl1Cards.SafetyTeamComments " _
                       & "FROM tbl1Cards " _
                       & "WHERE tbl1Cards.CardNumber = " & Me.txtFindCard & ""
                       
                If DCount("*", "qryFeedback", "CardNumber = " & Me.txtFindCard & "") > 0 Then
                        Me.Form.RecordSource = strSQL
Highlighting in the recordsource line.

EDIT: On Immediate window I catched the strSQL value as Access sees it, to me it does not seem to be wrong. But the odd thing is this error raising only ont he second update of the recordsource.

?strSQL
SELECT tbl1Cards.CardID, tbl1Cards.CardNumber, tbl1Cards.TeamSupervisorFK, tbl1Cards.FeedbackOn, tbl1Cards.StatusCardFK, tbl1Cards.ClosedOn, tbl1Cards.Description, tbl1Cards.DescriptionPor, tbl1Cards.FeedbackMessagePor, tbl1Cards.FeedbackMessage, tbl1Cards.SafetyTeamComments FROM tbl1Cards WHERE tbl1Cards.CardNumber = 1931234
__________________
Best regards,
Diogo Cuba

"Stay Hungry, Stay Foolish: you only live once, so make it count and go after what you want no matter what."

Last edited by DiogoCuba; 01-10-2019 at 12:17 PM.
DiogoCuba is offline   Reply With Quote
Old 01-10-2019, 02:01 PM   #12
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 7,872
Thanks: 98
Thanked 1,956 Times in 1,820 Posts
isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light isladogs is a glorious beacon of light
Re: Resetting formīs controls return a couple of errors.

Hi

That was the error you mentioned previously that I hadn't seen myself
It happens when you change card numbers as you are trying to change the form record source whilst it is in use
Its nothing to do with the combo box as it occurs with the textbox as well

For now you can solve the error by clicking the Reset button before changing the combo value. However that's just a temporary kludge. Even if you revert to a textbox you still need to sort this out

Basically you need to completely redesign this form as a form & subform as in the screenshot. Link form & subform by CardID and / or CardNumber

You will then need to scrap almost all of the code and start again BUT the good news is the new code will be VERY MUCH simpler than it was before.

I'm busy on another pressing project so don't have time to do any more now.
See how you get on and come back to me if you have problems
Attached Images
File Type: png Capture.PNG (27.0 KB, 19 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
DiogoCuba (01-11-2019)
Old 01-10-2019, 02:55 PM   #13
DiogoCuba
On Error GoTo AWF *****:
 
DiogoCuba's Avatar
 
Join Date: Dec 2018
Location: Rio de Janeiro - Brazil
Posts: 83
Thanks: 62
Thanked 1 Time in 1 Post
DiogoCuba is on a distinguished road
Re: Resetting formīs controls return a couple of errors.

Quote:
Originally Posted by isladogs View Post
...
Basically you need to completely redesign this form as a form & subform as in the screenshot. Link form & subform by CardID and / or CardNumber
...
See how you get on and come back to me if you have problems
Thanks for the explanation, Colin!

I will start working on the Form/SubForm iterations tomorrow, if I need any help I will get back to you, but I hope I will be able to start fishing myself...
__________________
Best regards,
Diogo Cuba

"Stay Hungry, Stay Foolish: you only live once, so make it count and go after what you want no matter what."
DiogoCuba is offline   Reply With Quote
Old 01-11-2019, 12:56 PM   #14
DiogoCuba
On Error GoTo AWF *****:
 
DiogoCuba's Avatar
 
Join Date: Dec 2018
Location: Rio de Janeiro - Brazil
Posts: 83
Thanks: 62
Thanked 1 Time in 1 Post
DiogoCuba is on a distinguished road
Smile Re: Resetting formīs controls return a couple of errors.

Quote:
Originally Posted by isladogs View Post
...
Basically you need to completely redesign this form as a form & subform as in the screenshot. Link form & subform by CardID and / or CardNumber
...
See how you get on and come back to me if you have problems
Hey Colin, I have just finish the revamp and recoding of the form, however I have not used the Form/Subform approach you suggested.

The solution I used was Allen Browneīs "Using a Combo Box to Find Records" plus a couple of the tweaks you provided and some from my side. I also included the Error Handler to trap errors as you mentioned, later I will apply what I have learnt to the other forms.

Allen also have another option for combobox with tens of thousands records, I will take a look at this option later because this will be the situation this particular project will be once I import the real database to it.

The entire code is in the file attached if anybody would like to see/use. I am stil working on the UI though.

The form that is being discussed is the frmNewFeedback.

Thank you a lot for the time and for the help!

****************EDIT***************************
After playing a little with the form I decided to add a Data Validation and it start triggering error 2046. Digging the forum I found a answer from RuralGuy about this so I am posting the fixed code, the lines 20-40 are the suggested option by RG:

Code:
 
Private Sub cmdSave_Click()
...
                ' SAVE THE RECORD
20                If Me.Dirty Then
30                  DoCmd.RunCommand acCmdSaveRecord
40                End If
...
Attached Files
File Type: accdb SOC Mgr - Stripped_v5_DC.accdb (1.66 MB, 11 views)

__________________
Best regards,
Diogo Cuba

"Stay Hungry, Stay Foolish: you only live once, so make it count and go after what you want no matter what."

Last edited by DiogoCuba; 01-11-2019 at 04:00 PM. Reason: Include Correction of Code
DiogoCuba is offline   Reply With Quote
Reply

Tags
error 2105 , error 2107 , events , form

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Controlling the return or enter key in controls darbid Forms 2 04-28-2009 02:55 AM
A couple of form questions BTaylor Forms 1 05-15-2008 07:40 AM
Run-time errors, coding errors, a catalogue of errors nikkypickles Modules & VBA 4 11-27-2006 02:26 AM
Return Errors to Table BrokenBiker Modules & VBA 3 10-02-2006 12:02 PM
Re: Need help in saving & resetting controls in a subform alicejwz Forms 0 12-16-2004 10:01 AM




All times are GMT -8. The time now is 08:37 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World