Solved SubForm [Continuous Form] not updating data.

[mloucel]

MODERATORS PLEASE:
I tried to attach my database but no matter how small, just the essentials, no data, Zipped and I still keep getting the same error message:
" The Uploaded File Is Too Large "
No zipped is: 14,336 kb
Zipped is: 9,130kb

Good [morning/afternoon/evening] wherever in the world you are.

I am working on a modification on a form my boss wants in our new software.
[This part WORKS fine]
The form is to ADD basic new patient information.
-Works by searching the input data, then:
-- If NOT found then ADD the Data to the Database
--- Requery the Subform
--- CleanUp the fields and return to the 1st one to start all over again

- IF FOUND [Here is my problem]
-- Should simply Requery the SUBFORM to display ONLY the record(s) that are found.
but instead of doing such it goes to BLANK

I tried to Upload the database but I keep getting an error [maybe I have my account restricted]

--- Check the section called:
'------------------------------------------------------------
' If record not found, add a new record
'
'------------------------------------------------------------

I enter details on where the issue is
The form has:
1 main form with 4 text fields
1 search button [ Private Sub SearchBtn_Click() ]
1 Reset Button

Please see in the code the NOTE that says:

' I am Having Issues Here ...


Here is my Code:

Option Compare Database
Option Explicit
Dim NR As Boolean
Dim UseButton As Boolean

'------------------------------------------------------------
' Form_Load
'
'------------------------------------------------------------
Private Sub Form_Load()
    DoCleanUp
End Sub

'------------------------------------------------------------
' PDOB_Click Patient's Date Of Birth call the new form
'
'------------------------------------------------------------
Private Sub PDOBTxt_Click()
    InputDateField PDOBTxt, "Select the DOB"
End Sub

'------------------------------------------------------------
' ResetBtn_Click Reset all the fields
'
'------------------------------------------------------------
Private Sub ResetBtn_Click()
    DoCleanUp
End Sub

'------------------------------------------------------------
' SearchBtn
'
'------------------------------------------------------------
Private Sub SearchBtn_Click()
    ' Check if ALL text boxes are filled with Data
    If Not DataValid(Me) Then
        Exit Sub
    End If
    
    Dim strSQL As String
    Dim rs As DAO.Recordset
    Dim Found As Boolean
    Dim RecordsFound As String
    
    ' Convert the first letter to Uppercase
    If Not IsNull(PLastNameTxt) Then PLastNameTxt = fnCapitalizeFirstLetter(PLastNameTxt)
    If Not IsNull(PFirstNameTxt) Then PFirstNameTxt = fnCapitalizeFirstLetter(PFirstNameTxt)
    
    ' Construct the SQL query
    strSQL = "SELECT * FROM PatientT " & _
             "WHERE PLastName = '" & Me.PLastNameTxt.Value & "' " & _
             "AND PFirstName = '" & Me.PFirstNameTxt.Value & "' " & _
             "AND PDOB = #" & Me.PDOBTxt.Value & "#;"

    'MsgBox strSQL
    
    ' Open a recordset based on the query
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    ' Check if any records are found
    If Not rs.EOF Then
        Found = True
        rs.MoveFirst
        Do Until rs.EOF
            RecordsFound = RecordsFound & "Name: " & rs!PLastName & " " & _
            rs!PFirstName & ", DOB: " & rs!PDOB & vbCrLf
            rs.MoveNext
        Loop
        rs.Close
        'MsgBox RecordsFound
    Else
    
        Found = False

    End If
    
'------------------------------------------------------------
' If record not found, add a new record
'
'------------------------------------------------------------
    If Not Found Then
    
        Set rs = CurrentDb.OpenRecordset("PatientT", dbOpenDynaset)
        rs.AddNew
        rs!PLastName = Me.PLastNameTxt.Value
        rs!PFirstName = Me.PFirstNameTxt.Value
        rs!PDOB = Me.PDOBTxt.Value
        rs!PPhone = PPhonetxt
        rs.Update
        rs.Close
        Me.FoundRecordsSF.Form.Requery
        
        MsgBox "Record added successfully!", vbInformation
        
        DoCleanUp
    Else
'------------------------------------------------------------
'
' I am Having Issues Here ...
'
'------------------------------------------------------------
        Found = True
        ' Assign the recordset to the subform's record source
        MsgBox strSQL
        Me.FoundRecordsSF.Form.RecordSource = strSQL
        ' Requery the subform to display the found records
        Me.FoundRecordsSF.Form.Requery
    End If
End Sub

'------------------------------------------------------------
' CleanUp routine
'
'------------------------------------------------------------
Sub DoCleanUp()

    PLastNameTxt = ""
    PFirstNameTxt = ""
    PDOBTxt = ""
    PPhonetxt = ""
    PLastNameTxt.SetFocus
    
End Sub

 

[Gasman]

Yes, every site has limits.
You only need enough to show the problem.
I always refer to form controls as Me.

[maybe I have my account restricted]

No,. your file is too big.

Why do you do CleanUp on form load?,
You seem to be creating functions for ones that alreday exist in Access?

VBA Upper, Lower, and Proper Case - Case Functions

This tutorial will demonstrate how to use the UCASE, LCASE and STRCONV functions in VBA. While working in VBA, you often need to convert strings into

www.automateexcel.com

Walk your code with F8 and breakpoints and see what it is actually doing, not what you think it is doing.
Failing that, create a new db from your db with just enough to show the issue, with instructions on how to recreate the issue.

 

[mloucel]

Yes, every site has limits.
You only need enough to show the problem.
I always refer to form controls as Me.


No,. your file is too big.

Why do you do CleanUp on form load?,
You seem to be creating functions for ones that alreday exist in Access?

VBA Upper, Lower, and Proper Case - Case Functions

This tutorial will demonstrate how to use the UCASE, LCASE and STRCONV functions in VBA. While working in VBA, you often need to convert strings into

www.automateexcel.com

Wlak your code with F8 and breakpoints and see what it is actually doing, not what you think it is doing.
Failing that, create a new db from your db with just enough to show the issue, with instructions on how to recreate the issue.

Thanks for your suggestions,

The CleanUp was actually a leftover from the OLD program I had for this sub routine, is clean now.

I had the impression that the function vbProperCase didn't check names like "Paul Anthony" hence I found that alternative, but I tested a few times and seems like it works so I will change my code.

with the last suggestion I have tried the F8 option a few times and did the msgbox strSQL, everyting seems to be fine, but it doesn't work, and there is where I am stuck.

Thank you for your input.
Maurice.

 

[tvanstiphout]

set a breakpoint where strSQL is constructed.
In the Immediate window:
?strSQL
Copy that text, and paste it into a new query, switched to SQL view. Switch to design view, and the parser may point out the errors of your ways. If no parser errors, at least you can use this as a starting point for understanding why it "doesn't work" (one of my least-favorite phrases in a technical forum).

 

[mloucel]

set a breakpoint where strSQL is constructed.
In the Immediate window:
?strSQL
Copy that text, and paste it into a new query, switched to SQL view. Switch to design view, and the parser may point out the errors of your ways. If no parser errors, at least you can use this as a starting point for understanding why it "doesn't work" (one of my least-favorite phrases in a technical forum).

Done
The SQL instruction did work, no problem, it actually game exactly the record found, display it without any issue.
So that leaves the SubForm and I am guessing how is built or something..

I created the form using the Table as record source, then I just simply add the records I need to the form, nothing else very simply and straight forward.

Then add that form to my main form, so now is a SubForm.
If there is a NEW record it works like a charm.
If FOUND then
Testing the SQL also works
but it does not do anything to the subform.

 

[Gasman]

So why are you not using bound forms?

 

[Pat Hartman]

I agree, this takes almost no code with a bound form.

When searching for proper names, be aware that they may contain apostrophes' which will make your search code break. One solution is to surround the search string with double quotes instead of single quotes.

 

[Gasman]

I cannot see a glaring error, so would need a db to work on, and do what I said you should do.

 

[mloucel]

I agree, this takes almost no code with a bound form.

When searching for proper names, be aware that they may contain apostrophes' which will make your search code break. One solution is to surround the search string with double quotes instead of single quotes.

HA... GOTCHA [for me...]

That was the reason of that particular subroutine and why I didn't use the proper name function of access, I had an issue and was breaking my head, someone send me that code and Problem Solved.
So I will go back to the subroutine.

So why are you not using bound forms?

Hey @Gasman , I am using a "Continuous Forms" I've never used one of those if the form is not bound to something.
I do not posses your knowledge and if you can point to me to where is the error, that would be great, as well as I can put my database maybe in my OneDrive so you can check it.

 

[Gasman]

All you need is whatever supports this form.

Create a new DB and import ONLY what you need to do that.
If you have a stupid amount of records, delete most of them.

JUST ENOUGH to show the issue.

If you are worried about confidential data then use the code here.

 

[mloucel]

All you need is whatever supports this form.

Create a new DB and import ONLY what you need to do that.
If you have a stupid amount of records, delete most of them.

JUST ENOUGH to show the issue.

If you are worried about confidential data then use the code here.

Hello...
The data in the DB is just makeup names and dates not one is real, all of them are pure inventions of my imagination for test purposes only, no confidential data whatsoever.

 

[Pat Hartman]

A form is a form is a form. There are three views
Single
Continuous
Data Sheet

But, all the code that runs in the class module of a form refers to a SINGLE record. It refers to the CURRENT record, which in continuous or DS view, might not even be visible. It could be scrolled off what is viewable. If the form is not bound to a recordset, I don't know how you would ever be able ro reference anything other than a single "current" record.

A form has a single set of properties. That is why when you change property settings on a form in continuous view, ALL the visible rows change color or hide/show something, etc. The data in each row is different because it is the recordset that holds the data.

A form is a form is a form whether or not it is the main form or a subform embedded in a subform control. The same rules apply. From within the form's class module - "me" refers ONLY to the current record. If from the subform you want to refer to a parent form control, you use "Me.Parent!somecontrolname". If from the parent you want to refer to a subform control, you use "Me.subformcontrolname.form!somecontrol" --- BUT --- you are always ONLY referencing the CURRENT record of the subform.

In either case, if you ever want your code to refer to a record that is not current, you would need to use the RecordsetClone and find the record you want to make it "current" in the recordset. Remember - you only ever reference the current row regardless of what method you are using with VBA.

 

[mloucel]

A form is a form is a form. There are three views
Single
Continuous
Data Sheet

But, all the code that runs in the class module of a form refers to a SINGLE record. It refers to the CURRENT record, which in continuous or DS view, might not even be visible. It could be scrolled off what is viewable. If the form is not bound to a recordset, I don't know how you would ever be able ro reference anything other than a single "current" record.

A form has a single set of properties. That is why when you change property settings on a form in continuous view, ALL the visible rows change color or hide/show something, etc. The data in each row is different because it is the recordset that holds the data.

A form is a form is a form whether or not it is the main form or a subform embedded in a subform control. The same rules apply. From within the form's class module - "me" refers ONLY to the current record. If from the subform you want to refer to a parent form control, you use "Me.Parent!somecontrolname". If from the parent you want to refer to a subform control, you use "Me.subformcontrolname.form!somecontrol" --- BUT --- you are always ONLY referencing the CURRENT record of the subform.

In either case, if you ever want your code to refer to a record that is not current, you would need to use the RecordsetClone and find the record you want to make it "current" in the recordset. Remember - you only ever reference the current row regardless of what method you are using with VBA.

Pat you are ABSOLUTLY right, I never thought about that [NEWBIE]...

Monday I will modify my form so that instead of trying to display a continuous form I have another code that displays on a text message,
this way it will show the duplicate record at my will of text / color and size.

Thank you so much for your insightful tip and for solving my issue, hats off to you Sir.

Maurice.

 

[Pat Hartman]

You're welcome Maurice.

Unbound forms require a lot of code because you have to duplicate all the functionality that is built into Access. Most people, when they use them, actually bind them to recordsets held in memory rather than to tables or queries. This is kind of a semi-bound form. But, they do it because in some cases, depending on your BE and where it is, Access can be slow to retrieve recordsets.

I've been using Access since the early 90's and so far have never had to use an unbound form to manipulate data. My SQL Server BE's have always been on fairly fast LANs so I've never had slowness issues with bound forms or working with recordsets in VBA.

 

[mloucel]

You're welcome Maurice.

Unbound forms require a lot of code because you have to duplicate all the functionality that is built into Access. Most people, when they use them, actually bind them to recordsets held in memory rather than to tables or queries. This is kind of a semi-bound form. But, they do it because in some cases, depending on your BE and where it is, Access can be slow to retrieve recordsets.

I've been using Access since the early 90's and so far have never had to use an unbound form to manipulate data. My SQL Server BE's have always been on fairly fast LANs so I've never had slowness issues with bound forms or working with recordsets in VBA.

Like I said hats off to you sir.
I appreciate your patience, I'm 60 trying to evolve from FoxBase [Yep that OLD] and in order to keep my Job I have to adapt to this new world, I left programming for about 20 to 25 years for a new adventure in the same field but as a IT Admin, now the company move that to the cloud and there was no more need for me, I saw the opportunity of keeping A JOB not my old job, by learning Access and creating access applications from old Excel forms the company was using, and trust me there are a lot of them, so here I am.

I do fully understand that my questions may sound stupid and dumb, but hey I rather be Stupid and Dumb once that all the rest of my life.

I appreciate your input in solving my issue as well as the other people that tried to help me, they as well are an asset to this forum.

Maurice.

 

[Pat Hartman]

my questions may sound stupid and dumb,

Questions are never stupid or dumb. They are how we all learn. I'm almost old enough to be your mother BTW. Just don't tell anyone I enjoy helping people so I hang out here and answer questions and give advice. I also develop software for charities when I can and I even have a product that is sold to the public which I still upgrade occasionally to keep the customers happy and renewing every year. The rest of the time I play Bridge although I hate airports so much that I no longer fly to events in other parts of the country the way I used to. If I can't drive, I don't go. What i just explained is a big picture concept which many don't actually see for years but it helps to put forms into perspective.

 

[mloucel]

Questions are never stupid or dumb. They are how we all learn. I'm almost old enough to be your mother BTW. Just don't tell anyone I enjoy helping people so I hang out here and answer questions and give advice. I also develop software for charities when I can and I even have a product that is sold to the public which I still upgrade occasionally to keep the customers happy and renewing every year. The rest of the time I play Bridge although I hate airports so much that I no longer fly to events in other parts of the country the way I used to. If I can't drive, I don't go. What i just explained is a big picture concept which many don't actually see for years but it helps to put forms into perspective.

Hey Pat.
I was able to do it in a different way, I found out that Mr. Richard Rost, has a form and code he sells on his website that is very ingenious, hence the reason I cannot post the code here since he makes money out of it.

In case someone is interested the only thing I can [And since you are a Super moderator if I violate any rule, please kindly apologize me I can amend it].
But this is a link to His website and to the program I am using now.

Search and Sort Template

It does solve my problem and way more. so, I found a light at the end of the tunnel, the code is simply amazing and so simple.

 

[Pat Hartman]

Rick has some great learning tools. That didn't sound like your initial question but It is an excellent sample. Glad it helped you. I think Rick recently joined this forum. I don't remember his name but you may find him lurking around

 

[mloucel]

Rick has some great learning tools. That didn't sound like your initial question but It is an excellent sample. Glad it helped you. I think Rick recently joined this forum. I don't remember his name but you may find him lurking around

Yes you are right, hence the reason I added that I was able to do it in a different way, you know the old saying..
"There's more than 1 way to skin a cat... "
Pat I am more than grateful for your kindness and patience.

Regards.
Maurice.