Solved Recordset Class and display all Records

[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

 

[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!!

 

[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.

 

[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?

 

[silentwolf]

Thanks Pat