Search results

HI Ranman, Thanks for the quick response. Firstly, I assume you meant acImport, so the query parameter isn't needed? Unfortunately, the VB method fails as stated in my original post & the macro method also fails, the error message is..."The MS Access engine could not find the object....etc'...

Hi All, I appreciate there are numerous posts on importing from Excel, but I've not yet managed to find one that solves my problem. How do I important and excel table with headers into Access (If its possible, as Excel tables are dynamic ranges)? I can import into a new temporary table using...

Thanks again Doc. If it helps others, stepping through the code etc didn't identify where the issue was being caused (at least for a novice like me). I identified the culprit procedure by logically & systematically preventing subs from being called from the main procedure until I found the...

Hi All, Just to closeout, whilst not 100% sure, I think the form was hanging due to SQL running in the background even though the VBA fully completed and DoEvents are used. Essentially, there was one procedure that contained nested DAO.Recordsets to build a SQL script and then run the SQL...

Thanks for the feedback.....I've narrowed down to a sub that runs some SQL. I've added a line at the beginning of the culprit sub to exit (so sub gets called, sub does nothing exits & continues with main macro) and the freeze doesn't occur. There's a few things going on in this sub.... Loop...

Hi, Ranman, yes after the message box is displayed, I click OK then the vba steps to the next & last line End Sub. JHB - I've done all that multiple times. Error handling for all except 2 procedures is as follows: On Error GoTo Error_Handler 'Do stuff Exit_Handler: 'Nothing to clear...

Hi All, I have a an Access 2013 accdb database being developed, however I now have a problem where it consistently freezes in the same place (I'm on 64 bit laptop, windows 7). I have a specific form whereby a command button runs VBA to import 2 files, convert the data based on a set of rules...

Hi All, Can anyone please advise on the best approach for copying records for archiving purposes. Scenario: I need to update records that meet criteria, but the user may later need to see the original record, what changes took place and the updated record. My thoughts were to have...

Thanks Sneuberg, looks inline with what I'd found. I'll try your code over the weekend. Much appreciated

OK I may have found a solution, but will test over the weekend. In summary, I understand I need to: 1.loop through ImportExportSpecifications to find the spec by name. 2.When found, update the XML "Path" parameter. Sounds simple enough Tooley

Hi All, Is there a way to amend the file path & name of an existing import spec, via VBA? Nothing else should change. FYI, this is to future proof the specs, so if the file location or name changes (which is highly likely this year due to changes), the user can remap by selecting the file via...

Thanks Minty. I think it may have been the requery causing the user having to click twice. I've changed the module to the following, which appears to be working as requied: Option Compare Database Option Explicit Const ps_SQL As String = _ "SELECT tblLULItms.ListItem" _ & "...

Hi All, I am developing a simple query builder which enables the user to create rules to automate data changes. When creating the criteria, the user selects a FieldName, Operator & Search value. The operators available are based on the Table.Def DataTypeName for each subform record. The...

It's obvious now you've said that, thanks :)

Hi All, I have 'custom query builder' in a main (Header) & sub form (Criteria), where the user selects a field name (combo) from a table, operator (combo), & value (textbox). I also have a function to return the TableDef DataTypeName of the Field name selected, which works as expect for a...

To assist others, my final code is below which does not involve sub forms: CommandButton events: Private Sub cmdPriorityOrderIncrease_Click() Call sPriorityOrderChange(True) End Sub Private Sub cmdPriorityOrderDecrease_Click() Call sPriorityOrderChange(False) End Sub Main procedure...

Thanks all for the suggestion, which I've followed and used the code from Cronk which works exactly as I wanted. Much appreciated :)

Hi All, I have a Access 2013 database which imports a dataset from MS Excel, makes changes based on a set of rules (setup in another table and run via VBA using DAO recordsets), then exports the amended dataset. The rules need to be assigned a number for order of priority they are to be...