Search results

If you find/develop anything I'd definitely be interested spent hours the other day trying to find where I'd used < and not >

So I'm trying to get the following code to only run once per change. The issue being changing 1 value in the document can cause 100s of changes on the sheet this code is associated with and thus it runs 100s of times not just once per change. What do I need to do/add to make this only run once...

that's a fair point i keep finding myself saying that at work, almost always means i'm forgetting something obvious or need to go find more information

could you not just use a query to return only the fields you need and the copyrecordset method to get the data in excel then move it around to where you need it?

If I'm going to split it into lines I tend to do it like this, it always goes horribly wrong and takes an age to debug when I use _ strSQL = " UPDATE [patient] " strSQL = strSQL + "SET [Current_Med] = '" & MyMed & "', " strSQL = strSQL + "[SType1_Desc] = '" & Me.SType_1 & "', "...

this might work i find it easier to write SQL on a single line or build the statement in parts then you don't miss any of the quotation marks as you'll get a compile error strSQL = "UPDATE [patient] SET ([patient].[Current_Med] = '" & MyMed & "', [patient].[SType1_Desc] = '" & Me.SType_1 & "'...

yeah I follow that makes sense really if you're clicking a button to do it may as well update the single field in the table directly, if you're typing it in have it save on lost focus sort of thing

Ah fair didn't know about that method as all my forms pull data from different tables all over the place and updates have to be saved with a button press and only certain fields are allowed to be edited so for me that code is the simpilest way I've found

could you not just update the table with sql? docmd.runsql "UPDATE tblName SET tblName.fldToChangeName = "D" WHERE tblName.fldRecordID = recordID;"

hmm I thought that answer I gave didn't make sense try adapting this that I just wrote, I've tested it ant it does what you're after instead fo the print strplit you'd have your sql Sub splitem() Dim strlong, strsplit As String, varsplit, var As Variant, i As Long i = 0 strlong =...

I can see that null vs zls is potentially a big issue but I also see why I've only just encountered it as this if the first time I've looked for empty fields in this database. From this i'm gathering that transferespreadsheet acImport moves blank cells over as nulls which is at least consistent

ah I see it's a dimensional issue null represenst a lack of data rather than blank data, fair enough you'd think I'd have picked up on that by now

I'd quwstion the logic of having the numeric filed called ID if it's representative of a quantity as for the type mismatch i'm going to guess you meant to put strcolours in there?

solved it just used isnull(tblA.Fields(field2)) = true still don't really know why a null text field doesn't trip the = "" honestly i don't think the rest of the code wouldn't have made a difference more if statments and loading the recordsets is all that's there

for some reason this if statement never returns true and builds the sql statement and i can't for the life of me work out why background to this is importing a load of updated data using transferspeadsheet replacing current table which has been renamed it then should fire up a load...

glad to help the little mistakes like that can be the most infuriating

shouldn't it be DoCmd.RunSQL mySQL

Ok so I've finished being a bit dim and now the code runs fine (final code below if anyone wants it though not commented for the now) So back to my original question, is there a more efficient way of doing this as this is some what processor intensive due to the number of itterations across...

ok so I went ahead and coded something anyway but why on earth won't it work i feel like i'm missing something fundementally obvious here which doesn't help. I keep getting the item not found in collection error but it works perfectly fine if i type the field values in Option Compare...

Just a quick one I suspect. I'm currentyl using transfer spreadsheet to import 2 tables, I've just recieved a new data set and my boss now wants to know what's changed where as before he was happy to just overwrite the old data. I'm thinking I'll need to import the data into a new table run...