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
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:
Code:
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?
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...
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
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.
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.
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'
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.
Code:
Sub Combine()
UpdateGutschriften
UpdateSEPALastschriften
UpdateLastschriften
UpdateZahlungINET
End Sub
Code:
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.
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.
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?
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