Solved Recordset Class and display all Records

[CJ_London]

no - was just showing you how you can utilise one function/sub rather than having multiple functions/subs - which I thought was your objective.

I would think updating a table via a select query would be about the same as updating a table directly. Just not sure why you would want more queries - seems like you want to keep things complicated . But then I've not seen your data or clear what the objective is beyond updating some fields based on data in another field. It may be all fields could be updated in one update query

 

[silentwolf]

Well I try to explain so you understand what my objective is.

There is one Field in my Data which is combined and the string is massive about 300 odd Characters.

I split this field into more fields so I can get the information I need in more suitable manner.
The Problem however is that the original Field is not following a certain concept or better it veries
both in Categories or in the whole field.

It does not follow constant way.

My fields will then get filled with just a appropiate or part of that massive string.

To manage the update I created now different Queries according to those Cathegories so it is easier to have a custom function returning
all the infos I need and then fill those fields accordingly.

What I send you was just one part of the Query, now I put all those Queries into one well not all yet but getting there:

something like that:

Sub Combine()
    Dim strSQL As String
   
    strSQL = "UPDATE qryGutschriften SET qryGutschriften.Zahlungsreferenz = GutschriftZahlungsref([UMSATZTEXT]), qryGutschriften.Auftraggeber = AuftraggeberReference([UMSATZTEXT]), qryGutschriften.Umsatztext = UpdateGutschriftUmsatztext([UMSATZTEXT]);"
    CurrentDb.Execute strSQL, dbFailOnError
   
    strSQL = "UPDATE qrySEPALastschriften SET qrySEPALastschriften.Mandatsnummer = Mandatsnummer([UMSATZTEXT]), qrySEPALastschriften.Umsatztext = UpdateGutschriftUmsatztext([UMSATZTEXT]);"
    CurrentDb.Execute strSQL, dbFailOnError
   
    strSQL = "UPDATE qryLastschriften SET qryLastschriften.Mandatsnummer = Mandatsnummer([UMSATZTEXT]), qryLastschriften.Zahlungsreferenz = LastschriftZahlungsref([UMSATZTEXT]), qryLastschriften.Umsatztext = UpdateGutschriftUmsatztext([UMSATZTEXT]);"
    CurrentDb.Execute strSQL, dbFailOnError
   
    strSQL = "UPDATE qryZahlungINET SET qryZahlungINET.Zahlungsreferenz = Zahlungsreferenz([UMSATZTEXT]), qryZahlungINET.Auftraggeber = AuftraggeberReference([UMSATZTEXT]), qryZahlungINET.Umsatztext = UpdateGutschriftUmsatztext([UMSATZTEXT]);"
    CurrentDb.Execute strSQL, dbFailOnError
   
End Sub

it all runs pretty nicely but maybe there is again a better way?
Is that or could I leave it like that or is there a problem with it?

 

[CJ_London]

as I said before without seeing your data and identifying which parts of the string you want to keep, I can't advise

But if it is working for you then leave as is

 

[silentwolf]

as I said before without seeing your data and identifying which parts of the string you want to keep, I can't advise

I understand but it is very difficult to send that data as there are so many different ways as said.

But if it is working for you then leave as is

So there is no issue with that Queries as how it opperate? Nothing to change?

 

[CJ_London]

without seeing your data......

 

[silentwolf]

I just meant the structure of that query.

Sub Combine()?

Is it syntactly right?

 

[theDBguy]

But I do not know how to set it up to get all records back from it.

So I can display those records if needet.

I haven't read the entire thread and just saw the above section. Is that part still an issue? If so, have you tried the GetRows method? Just curious...

 

[CJ_London]

Regards syntax if it works, it works. You’ll get an error if it doesn’t which might be a syntax error or it might be a runtime error. Run it and see- use a copy of your destination table until you are sure it is working ok

 

[Pat Hartman]

This brings me back to the 80's when we were transitioning From VSAM (indexed, direct access) and IMS/DB (hierarchical) to DB2 (relational). Some bright bulb decided to abstract I/O. The problem was that the methods of the three BE's we were using at the time were so different that there wasn't any real way to make them a class. He got it to work, sort of but it increased runtimes by from 10 to 25 percent depending on whether the app was processing or I/O bound.

 

[silentwolf]

Hi guys ..)
lots of comments since I have been away ..
@theDBguy

I haven't read the entire thread and just saw the above section. Is that part still an issue? If so, have you tried the GetRows method? Just curious.

I got a little side tracked I guess with the original post..
And did not got back into it but I will once the Data updates all as needed.
I will look into GetRows method and see how I go with it. Thanks for pointing it out!

@CJ_London
yes it works so should be good so far and pretty quick it runs now Very Happy..
just putting the rest work together and clean up the code as it looks a little like Dresden 45 lol if you know that saying.
Not meant to be rud of course!

And once all that is in place I should take the step to display the data whats needed and I should be good to go.

It s been a long journey with this project but finally I get to the end and it feeels good ;-)

@Pat Hartman
well back at those days I had nothing to do with coding so I am not really understanding what was back then but hope it is not that important nowadays in that respect.

So if I am stuck again I hope I can ask you for further advice... as it is great to have such experienced People here who do like to share their knowledge to novice like myself.

Cheers for that!!

 

[Pat Hartman]

The point was subtle - things that are already abstracted and optimized should not be further abstracted.

 

[CJ_London]

you can save yourself some typing

strSQL = "UPDATE qryGutschriften SET qryGutschriften.Zahlungsreferenz = GutschriftZahlungsref([UMSATZTEXT]), qryGutschriften.Auftraggeber = AuftraggeberReference([UMSATZTEXT]), qryGutschriften.Umsatztext = UpdateGutschriftUmsatztext([UMSATZTEXT]);"


could just be

strSQL = "UPDATE qryGutschriften SET Zahlungsreferenz = GutschriftZahlungsref([UMSATZTEXT]), Auftraggeber =AuftraggeberReference([UMSATZTEXT]), Umsatztext = UpdateGutschriftUmsatztext([UMSATZTEXT]);"

and perhaps to make it easier to read

strSQL = "UPDATE qryGutschriften SET" & _
                    " Zahlungsreferenz = GutschriftZahlungsref([UMSATZTEXT])," & _
                    " Auftraggeber = AuftraggeberReference([UMSATZTEXT]), " & _
                    " Umsatztext = UpdateGutschriftUmsatztext([UMSATZTEXT]);"

just make sure to include the spaces at the beginning of each line

You only need the table/query name if you have multiple tables and two or more of those tables have fields with the same name

When using the access gui, it includes the table name (as well and endless square brackets and parentheses) to keep the translation of the gui to sql 'simple'

You also don't need the final ';'

 

[silentwolf]

Ah I see says the blind man
Cool So I stick to it ..)

 

[silentwolf]

Ok cool all understood thanks for clearifying that to me too!!

Cheers CJ_London ! And of course for your help throughout!!

Much appreciated!!

 

[MajP]

Personally I try to have every sub do only one thing. This allows more flexibilty and makes error checking a lot easier. I see it way to often where people try to eat the elephant in one bit.

 Sub Combine()
    UpdateGutschriften
    UpdateSEPALastschriften
    UpdateLastschriften
    UpdateZahlungINET
End Sub

Public Sub UpdateGutschriften
    Dim strSQL As String
    strSQL = "UPDATE qryGutschriften SET qryGutschriften.Zahlungsreferenz = GutschriftZahlungsref([UMSATZTEXT]), qryGutschriften.Auftraggeber = AuftraggeberReference([UMSATZTEXT]), qryGutschriften.Umsatztext = UpdateGutschriftUmsatztext([UMSATZTEXT]);"
    'debug.print strSql
    CurrentDb.Execute strSQL, dbFailOnError
End Sub

Public Sub UpdateSEPALastschriften
   Dim strSQL As String
   strSQL = "UPDATE qrySEPALastschriften SET qrySEPALastschriften.Mandatsnummer = Mandatsnummer([UMSATZTEXT]),     qrySEPALastschriften.Umsatztext = UpdateGutschriftUmsatztext([UMSATZTEXT]);"
    CurrentDb.Execute strSQL, dbFailOnError
End Sub

....

Each part can be individually used, tested and debugged.

 

[Pat Hartman]

I try to have every sub do only one thing.

Very few people understand coupling and cohesion any more. It is good to see the concepts are not dead, just in hiding

 

[silentwolf]

Personally I try to have every sub do only one thing. This allows more flexibilty and makes error checking a lot easier. I see it way to often where people try to eat the elephant in one bit.

Ok no problem makes also sence for sure!

Thanks for the input!
Well I did it before like that for testing purpose...

See if I change it as the code now is alot smaller anyway as compare to the start of the session

 

[silentwolf]

A question remains to me.

How to you guys handle Code are you putting it into a module or straight to the FormModule?

This is something I still have struggel with to know which is better or should be more prefered?
As I pick the File from the Form I have at present all the Code in the form.
All Functions I store in a seperate module.

Is that how you would do it? Is there one way better then the other?

 

[Pat Hartman]

I try to have every sub do only one thing.

There isn't even any discussion about this.

If it applies to that form only, then put it in the form. Why would you put it anywhere else?

If you will use the code in multiple forms put it in a standard module and when you call it, pass in the form object so it can work with any form. Forms are classes after all. Or make a class for the class

 

[silentwolf]

Thanks Pat