Resetting form´s controls return a couple of errors. (1 Viewer)

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 09:56
Joined
Dec 24, 2018
Messages
150
Hi, :(

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

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.":confused:

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. :confused:

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!
 

Attachments

  • SOC Mgr - Stripped_v2_CR.accdb
    1.5 MB · Views: 295

isladogs

MVP / VIP
Local time
Today, 12:56
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • SOC Mgr - Stripped_v3_CR.zip
    166.9 KB · Views: 356
Last edited:

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 09:56
Joined
Dec 24, 2018
Messages
150
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,
 

isladogs

MVP / VIP
Local time
Today, 12:56
Joined
Jan 14, 2017
Messages
18,186
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
 

isladogs

MVP / VIP
Local time
Today, 12:56
Joined
Jan 14, 2017
Messages
18,186
LOL.
I do spend far too long on this site .... :rolleyes:
 

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 09:56
Joined
Dec 24, 2018
Messages
150
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.
 

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 09:56
Joined
Dec 24, 2018
Messages
150
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
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:56
Joined
Jan 14, 2017
Messages
18,186
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)
 

Attachments

  • SOC Mgr - Stripped_v4_CR.zip
    166.3 KB · Views: 345

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 09:56
Joined
Dec 24, 2018
Messages
150
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
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:56
Joined
Jan 14, 2017
Messages
18,186
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
 

Attachments

  • Capture.PNG
    Capture.PNG
    27 KB · Views: 201

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 09:56
Joined
Dec 24, 2018
Messages
150
...
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... ;)
 

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 09:56
Joined
Dec 24, 2018
Messages
150
...
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
...
 

Attachments

  • SOC Mgr - Stripped_v5_DC.accdb
    1.7 MB · Views: 344
Last edited:

Users who are viewing this thread

Top Bottom