Share a database? Am I on the right page?

[ReAn]

Sounds good.

 

[zuberal]

Thanks ReAn, I really appreciate that.

I had a look and I think I was on the right track, although I had put the Append query in the database that would be on the network. I'll play around with it tomorrow, and figure out how you did it. Especially that nice udpate button!

I'll let you know how I make out!

I guess my logic was to keep excellinked and excel query updater on the network database so that all of the admin stuff can be done there, so the end users don't see it. I think that will work too...

 

[ReAn]

Ah, your method would work just fine.

 

[zuberal]

Most excellent,

I think I have my head around this now. Couple things left...

When I run the append query, it adds the new records from my excel spreadsheet. (as long as something is entered in my primary key field which is the 9 digit number) Although, I still get the error which says that some records were not brought in due to key violations. This error is gone when I use my little form button with your code. (errors off) Although, the records that I update in the spreadsheet are still not reflected in the table after clicking the button.

Is the function of the append query to just add records to the table that don't exist via the primary key, or can it also update records that have changed regardless of primary key. Am I missing something? You mentioned something about an ID before...

Also, your button is all nice and centred in the screen when you run it. Mine just opens up and is way up in the left with a big grey background. My settings seem to match yours...where can I fix this?

Thanks again ReAn!

 

[ReAn]

All it does is add the new ones.

Match the form properties, it might help out ^_^

 

[ReAn]

Just read that link above, let me modify my query to update records as well.

 

[ReAn]

Try modeling your query like this one: may have to re-link tables...

 

[zuberal]

Just read that link above, let me modify my query to update records as well.

Thanks! ...I'll check it out. Which link above are you referring to? I'l check the form properties again...I thought they were matched...

A~

 

[ReAn]

the linked tables in my test db(s) for they may be absolute which would mean you need a user 'ablondah' on you're machine and a folder 'asdasd' on the desktop for you to put it in if you want it to work.

However, it may be storing the links as relative paths, in which case you are set.

 

[zuberal]

I found your stuff just required c:\blondah\my documents to work...so I'm good to go.

Looks like an update query. Again, I have to leave the office. Finally got my drive space which I've been waiting for for awhile. Looks like we're all set.

Much appreciated. Where do you live? Maybe I could send you a donut? Or a beer? lol

A~

 

[ReAn]

Haha, no worries about that, but you know my location is valid look up ^^^

 

[zuberal]

Haha, no worries about that, but you know my location is valid look up ^^^

Hello Dangerous programmer!

Calgary huh? Never been there!

So OK, I've tried both queries. The update one...you have to put all the fields. Works pretty good.

I guess there is no way to combine them into one big query, with one big button? Rather than two queries, and two buttons?

PS: How did you get that button to first pop up when you start Access?

 

[zuberal]

Hello Dangerous programmer!

Calgary huh? Never been there!

So OK, I've tried both queries. The update one...you have to put all the fields. Works pretty good.

I guess there is no way to combine them into one big query, with one big button? Rather than two queries, and two buttons?

PS: How did you get that button to first pop up when you start Access?

Hmm...I think update the code....I wonder if I could do that....can I just copy your code and just change the name of the new query in the code...just append it to the bottom?

 

[ReAn]

1) The update query if set up properly will update old records AND add missing ones.

2) Tools->Startup, Choose initial form.

 

[zuberal]

1) The update query if set up properly will update old records AND add missing ones.

2) Tools->Startup, Choose initial form.

AH! Nice long weekend.

Thanks for all your help ReAn...I'm starting to build the "real" DB now...then will continue on testing in the other.

Quick question in regards to your update query. May be a bit dumb..lol

In design view, I noticed that you have the table as the main table, and you have the spreadsheet in the "Update to" field. In other words, take the info from the table, and update it into the spreadsheet. Isn't that backwards? The info in the other append query is opposite. Or is this how the update query works? I already tested and it looks like the data comes over fine to the table from excellinked...although, when I update data in the forms and run the query, will the data also go back to the spreadsheet when the query is run? lol

 

[zuberal]

1) The update query if set up properly will update old records AND add missing ones.




2) Tools->Startup, Choose initial form.

I think I'm going to leave this as an update query and an append query.

In regards to the form, is there a way to make it dissapear or confirm and disappear after clicking the update button?

Also, I created two Advanced filters called "delete cities 1" and "delete cities 2" that run off of the main table. I saved them as queries. When we run each one, the results come up and then we delete the records. (then we confirm delete) Is there a way to automate this where they run and don't need confirmation at all? Will this require more code?

Thanks! (gonna owe a 24 before this is all over hehe)

 

[zuberal]

I think I'm going to leave this as an update query and an append query.

In regards to the form, is there a way to make it dissapear or confirm and disappear after clicking the update button?

Also, I created two Advanced filters called "delete cities 1" and "delete cities 2" that run off of the main table. I saved them as queries. When we run each one, the results come up and then we delete the records. (then we confirm delete) Is there a way to automate this where they run and don't need confirmation at all? Will this require more code?

Thanks! (gonna owe a 24 before this is all over hehe)

Hmmm...I just found delete query...perhaps that will be better for what I need to do...

 

[kousotsu]

Rtm?

While the forums are great for help, there are some of your questions that are easily answered by searching through the Access help files. The reading isn't all that intensive, and you seem like a smart enough cookie to catch on. I would recommend diving into the VBA help (using code in forms and such) headfirst. I only say this because it REALLY helped me back into the VBA swing. (I think Pat suggested it to me way back in March/April ^_^).

For instance, about the question of auto-closing the update form after a messagebox: in design view, right-click any item and click the "properties" selection. This will bring up the properties dialog. Using the object selection dropdown, find an object like a textbox or the form itself - essentially anything that looks manipulable by the user - and select the "Events" tab. Here, you'll find the various actions attributed to this form element (ie: beforeUpdate, onClick, etc). If you want a confirmation for the update first, follow this process:

· select your command button, open the properties dialog.
· select the "other" tab, and name the button according to conventions (cmd<WhatItDoes>).
· select the event tab, find "onClick" (when you focus on this element, a small button with an ellipsis will appear to the right - you can use this or the drop down to select "[Event Procedure]" - which you will use to open the code window.
· look up in Access Help how to use Y/N message boxes as a starting point for data operations - they're great for deciding simple cases (if yes, do this; if no, don't do this).

Then (provided you want the form to close regardless of what they click), in the end of each selection's code you'll put a line with a command ("DoCmd." element) that tells that particular form to close itself.

Once you start learning the VBA code this way, you'll be able to intuitively answer a lot of these questions on your own. I started doing that, and it's really helped me a great deal - Pat will tell you, I was really at a loss there in March/April. ^_^

Just my two bits on how to be successful with Access. The forums are great, but there are a LOT of duplicate questions floating around. You're not dumb, for the record - my boss is dumb. At least you understand the value of Access and the value of well placed questions. ^_^

The other thing that really helped me was the deprecated Object Model for Access. Hey, Pat - do you have a link to that somewhere? I can't find one, and the one I have isn't on my office machine.


Cheers!

- Z

 

[zuberal]

Thanks.

Thank you all for your help. You know, I left computers to get into Avionics (electrical aircraft maintenance) Now that you all have given me just a little taste of programming, I can see how easy it would be if you were properly trained. Me like. Me think me like to build! GRUNT! Now I regret not taking a few programming courses....but, I think I will over the next few years....will complement my support and avionics experience.

So what's the best to get training for these days? VB?

This is the final that I ended up with after playing around.

Private Sub Command0_Click()
On Error GoTo tagError
response = MsgBox("Do you want to Update?", vbYesNo + vbQuestion, "Continue?")
If response = vbYes Then
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryExcelappend", acViewNormal
DoCmd.Close acQuery, "qryExcelappend", acSaveYes
DoCmd.OpenQuery "qryExcelupdate", acViewNormal
DoCmd.Close acQuery, "qryExcelupdate", acSaveYes
DoCmd.OpenQuery "deletecities1", acViewNormal
DoCmd.Close acQuery, "deletecities1", acSaveYes
DoCmd.OpenQuery "deletecities2", acViewNormal
DoCmd.Close acQuery, "deletecities2", acSaveYes
DoCmd.SetWarnings True
Else
DoCmd.Close acForm, "Update / Append from Excel", acSaveYes
End If
Exit Sub
tagError:
MsgBox Err.Description
Exit Sub
Resume
End Sub

While the forums are great for help, there are some of your questions that are easily answered by searching through the Access help files. The reading isn't all that intensive, and you seem like a smart enough cookie to catch on. I would recommend diving into the VBA help (using code in forms and such) headfirst. I only say this because it REALLY helped me back into the VBA swing. (I think Pat suggested it to me way back in March/April ^_^).

For instance, about the question of auto-closing the update form after a messagebox: in design view, right-click any item and click the "properties" selection. This will bring up the properties dialog. Using the object selection dropdown, find an object like a textbox or the form itself - essentially anything that looks manipulable by the user - and select the "Events" tab. Here, you'll find the various actions attributed to this form element (ie: beforeUpdate, onClick, etc). If you want a confirmation for the update first, follow this process:

· select your command button, open the properties dialog.
· select the "other" tab, and name the button according to conventions (cmd<WhatItDoes>).
· select the event tab, find "onClick" (when you focus on this element, a small button with an ellipsis will appear to the right - you can use this or the drop down to select "[Event Procedure]" - which you will use to open the code window.
· look up in Access Help how to use Y/N message boxes as a starting point for data operations - they're great for deciding simple cases (if yes, do this; if no, don't do this).

Then (provided you want the form to close regardless of what they click), in the end of each selection's code you'll put a line with a command ("DoCmd." element) that tells that particular form to close itself.

Once you start learning the VBA code this way, you'll be able to intuitively answer a lot of these questions on your own. I started doing that, and it's really helped me a great deal - Pat will tell you, I was really at a loss there in March/April. ^_^

Just my two bits on how to be successful with Access. The forums are great, but there are a LOT of duplicate questions floating around. You're not dumb, for the record - my boss is dumb. At least you understand the value of Access and the value of well placed questions. ^_^

The other thing that really helped me was the deprecated Object Model for Access. Hey, Pat - do you have a link to that somewhere? I can't find one, and the one I have isn't on my office machine.


Cheers!

- Z

 

[EzDoesIt]

Error saving record updates

I am getting the same error that started this thread.

can't save design changes or save to anew database object because another user has the file open. To save your design changes or to save a new object, you must have exclusive access to the file.

I will try splitting the database to see if that resolves the problem, but what I don't understand is that I am not changing the design of the form. I am only trying to add a new record to an existing table using an existing form. Does anybody know what is making access think that I have a new object or a design change?

Any input appreciated. Thanks in advance, Tammy.