I hope I haven't posted this in the wrong section, but it is a VBA problem, so please forgive me if it should be elsewhere.
I have two tabs in a spreadsheet, both containing lists of parts codes. I want to programatically compare the first list against the second, and where the code isn't found in the second list I want to append it to the end of the list.
I have tried to trap the error 1004 that is raised when the part cannot be found...
TonyRandom
Last Activity: Jan 13 '12, 01:49 AM
Joined: Mar 16 '08
Location: Andover, Hampshire, UK
-
Excel Error 1004 in vba vlookup function
-
Hi NeoPa,
The solution seems to lie with the CreateObject line. I managed to read your application Automation insight, and gleaned what was necessary from there. So far it appears to be working well.
Thanks
Tony -
Hi NeoPa
I have now declared all string variables individually as suggested. The file selection behaviour is as expected, and I have AllowMultiSelec t set to false.
I am not sure how to run the application automation from the already open access session, as I have tried to set the objAccess variable as currentDB and commented out the OpenCurrentData base line. I am still having the issue that the automation works, then chooses not to....Leave a comment:
-
Inconsistent ImportXML Runtime Automation Error
I am experiencing an intermittent problem with importing an XML file into access using VBA. The import works fine, but when re-run again at a later time it errors out with a Run-time Automation Error as per the attached image.
Here is the code to the point that it errors out.
...Code:Function ImportRequest() Dim db As DAO.Database Dim rst As DAO.Recordset Dim fld As Field Dim fd As FileDialog Dim objfl As
-
Thanks chaps. I will go down the new FE route, and as Rabbit said, change all forms to read-only.
I had thuogh of this approach, but it is good to have it validated, so I don't waste my time running a fools errand. :)Leave a comment:
-
Access 2010 Security
I have an orders admin database that is currently only accessed by two Sales Administrators. I have been asked to allow read only access to the DB for the actual sales force. Would it be better to create a new FE for them to access, or is there anyway that I can secure the existing file by user? I don't want to hard code in usernames. -
Hello TheSmileyCoder,
I am using Office 2010 Professional, but have only recently upgraded to it. Previously used Access 2000, and I am still getting used to where things are.
I have noted your comments regarding the appropriate event, and will give it a try. Not quite sure how it will work though. I guess I just change the Me.ActiveContro l. references to me.ActualContro lName and put it in the Forms.Afterupda te event.
...Leave a comment:
-
I have cracked it! I am a little bit worried that I am going to have problems though. Would I get any advantage using a linked table?
Here is what I used
...Code:Private Sub memComments_AfterUpdate() Dim MyOldValue As Variant Dim MyNewValue As Variant Dim MyNewFieldValue As Variant MyOldValue = Me.ActiveControl.OldValue MyNewValue = Me.ActiveControl.Value MyNewFieldValue = MyNewValue &
Leave a comment:
-
Thanks Rabbit, I see what you are saying, I should set up a side table on a one-to-many relationship. I will be displaying the form in a datasheet view, and the comments are just for short notes for the life of an order.
I will keep pecking at it, but will probably go with a subdatasheet view using the linked table. :)Leave a comment:
-
TonyRandom started a topic Can I set a memo field so that so that it can retain its old & new values?in AccessCan I set a memo field so that so that it can retain its old & new values?
I have a comments field on a form. I want the field value to be copied to a variable, so that when the user updates or changes the value of the field, the field value will be something like:
so that a constant log is kept for changes to that field?Code:Dim OldValue as Variant Dim NewValue as Variant OldValue & vbCrLf & vbCrLf & NewValue & vbCrLf & Now
Would something like... -
Thanks Stewart,
I did say I am a newbie to the process loops, and this has been bugging me all afternoon. It worked first time.
Thanks again!
TonyLeave a comment:
-
For Each Next Problem
Hello Everyone,
I am having a spot if difficulty creating a process loop using the For Each Next procedure.
I basically have a field in a table that stores a file location, This is passed to a query which has two fields. One field is the original file location, and the other is a new file path and filname based on the primary key for the record.
The trouble is that it performs the required action on the first record in the query,...
No activity results to display
Leave a comment: