Search results

OK now it's throwing a 3129 error on this line; With db.CreateQueryDef("", APND_QUERY) As I mentioned before, this is uncharted territory for me. So I'm going to guess that APND_QUERY needs to be defined somewhere as the Append SQL. Given that this is not referencing any tables, I'm not...

Thanks for that, I've pasted that code verbatim into my DB, however this line; Set rs = db.OpenRecordset("tbl_grape1", dbOpenSnapshot, dbReadOnly) is throwing a Run time error 3001 (invalid argument). This is all new ground for me, so I am floundering somewhat :confused: P.S. who said trivial...

Following is the final code if anyone is interested in the future. It still contains a couple of MsgBox (commented out) used during development to prove code was picking up what I expected. Probably should mention that TBL_Grape2, had two filed GrapeID (PK) and Grape (text filed that was...

Thanks to all for their input (y)

The kludge I've developed to work around this is to; SetWarnings False On Error Resume Next SetWarning True (at the end of the procedure

No that just causes a key violation error the first time the append tries to enter a duplicate. The duplicate needs to be detected before the query tires to append it

Ok the code seems to be doing it job of looping through the records, separating the discrete items and inserting them into a new table. That part is working just fine. Thanks peeps :love: However, I'm using Dcount() prior to running the append query to test if the next value, to be added to the...

@Minty (y) I'll post the full code once it's running to my satisfaction

OK the code seems to be working now but one small hitch, it crashes when it encounters a string that contains an apostrophe for example Montepulciano d'Abruzzo. Given that there is only one record that contains an apostrophe, the very simple (and obvious) solution is to edit the data prior to...

Thanks @Minty it's currently a logic problem, that I will, continue to, tussle with. Should I have no joy, I will certainly avail myself of your kind offer :)

Clearly a deeper flaw in my logic. Back to the drawing board :sick:

It's been running for over 30 minutes now, and whilst it is manipulating copies of the data in the live tables, I am a little nervous about what I'll see when it finally finishes :eek:

Would have worked had I reversed the order of the parenthesis and square brackets :mad:

@Minty I'm very concious of avoiding the pitfalls of using spaces and reserved words in filed names :) The query is part of a much larger procedure to normalise a table that until now I was happy being de-normalised. It has to copy grape names into a new table and separate fields that contain...

Bingo :) thanks @Minty

I've been banging my head on my keyboard for some time of this piece of code QryAppend = "INSERT INTO TBL_Grape2 [( Grape )] " & _ "values ( '" & GrapeSendStr & "' ) ;" When it tries to run, I'm getting a "Syntax error in INSERT INTO statement". I'm sure it's something very...

This Sample DB might also do what you are looking for. Have a look at the form's On Current event and the Combo's After Update event

Perhaps this sample does what you are looking for https://www.access-programmers.co.uk/forums/threads/get-next-number-for-primary-field.143842/

My understanding was that Autonumbers should be used for nothing more than primary keys. As they can not be relied upon to produce consecutive number. Autonumbers can skip a number sometimes based on failed or retracted update attempts. I'm pretty sure that a quick search on the subject will...

Why not break that single field into two fields, one to hold the minimum amount and the other to hold the maximum amount?