Cannot alter query data

[jik_ff]

Sounds complicated, but I don't think it should be. Maybe I'm wrong...

I have a sub (continueous) form that takes the office number from the main form to look up the names of employees that use that office. For instance, if I select office 4028, the sub form will list off
Jason
Jenny
This data is pulled from a query that checks for "like OfficeNumber" and compares it to the staff list.

in the details of the form I want to put a button called remove so that if the person is no longer sharing that office, then they can be removed with a button click. The problem I am having is that I cannot alter the records in this specific query. I have another query that does a look up and lists only those names that have an extension assigned to them, and in that query I can alter the data. What restricts when you can do this?

[rvasquez]

Can you please attach a sample of your workbook?

Thanks!

[JosephP]

if your query does not contain any way of identifying a specific record then it won't be updateable

[jik_ff]

I'm not going to say that I am any type of wiz at Access. I kind of get what you are talking about, but isn't a look up based on a specific value identifying a specific record?

Well I kind of got around it. I took the query that was working, cleaned it up to match what I was looking for (same Like code) and that one works. Not sure what I did to the other one, but I can live with that for now. So in the sub form I can now make changes. Thing is I want to make a button that when click sets the officenumber to 0. I've looked this up and found the VB code for this to be something like this:
Private Sub RemoveName_Click()
Me.OfficeNumber.Value = 0
End Sub
but this doesn't seem to be working. I am not trying to delete the record, I want to set the officenumber to 0 so it is no longer seen in the look up. Again, I can go into the field and change it in the form, and that works. I would just prefer that it be a one click for the operators of this database.

[JosephP]

I'm not going to say that I am any type of wiz at Access. I kind of get what you are talking about, but isn't a look up based on a specific value identifying a specific record?

not necessarily - it could return multiple records or data from several tables.

Well I kind of got around it. I took the query that was working, cleaned it up to match what I was looking for (same Like code) and that one works. Not sure what I did to the other one, but I can live with that for now. So in the sub form I can now make changes. Thing is I want to make a button that when click sets the officenumber to 0. I've looked this up and found the VB code for this to be something like this:
Private Sub RemoveName_Click()
Me.OfficeNumber.Value = 0
End Sub
but this doesn't seem to be working. I am not trying to delete the record, I want to set the officenumber to 0 so it is no longer seen in the look up. Again, I can go into the field and change it in the form, and that works. I would just prefer that it be a one click for the operators of this database.

"doesn't seem to be working" doesn't really tell us a lot. does it cause an error, or do nothing, or do the wrong thing?

[jik_ff]

Well with my query, it only checks one table for it's data. The form is what takes a value from another table to use as the lookup. Again, I had all the same fields in both queries. I must have set something to cause that issue...

Sorry, nothing happens. I know that it may not refresh the data on the screen. I did 2 things to test this. First I changed the number by editing the field in the form. I closed the form then re-opened it and the change was apparent (the test record was not listed). I then reset the test record, closed all and ran the form again. I then tried pressing the button (a few times just in case). I close the form and re-opened it (as I did in the manual change test) and the test record was still there (the OfficeNumber did not change).

I don't really care how it is done, I would just like a button that would change this specific value to 0.

[JosephP]

it's be helpful to see the db file.

[jik_ff]

Ok, here is a copy of the file:
http://jikbyond.webs.com/Storage/Sam...ayout20121.TXT

This is a zip file, but I could not upload it here (work firewall issues) or to my outdated site as a zip. So I changed the extension to TXT, just change it back to ZIP and extract the access file.

The employee data is under #StaffList. I have 2 sample employees with an office number of 4028. You don't need to run the main form (it's all rough stuff right now anyway), just open the #OfficeAssignedSub form. at the prompt enter 4028, and you will see the 2 names listed. the officenumber field is unlabeled, but it will be hidden later, will be what you entered. If you go into the field, you can change the number, close the form and re-open, the entry will not show up. But the Remove button does not work.

[JosephP]

the remove button works for me. sets the control to 0.

[jik_ff]

Really? I just tried it again, and still nothing. I am using Access 2007. Are you using the same version? Is the change apparent right away in the form?

Could the VB script be different? I know there are issues with VB script over the years of Access, I recently had to fix up an access 2003 file that was limping along from access 98. The Scripts would crash once and a while.

Is there a way to do this with out VB? Expression code possibly?

Ok, my bad... Forgot about the security warning. When I accept that, it does work. Ok, a side to this issue is that the people that will be using this form will need something simple. It will be shared off the network, but I don't want them to have to click the security warning all the time...

[JosephP]

no, I'm using 2010 and yes, the change happens soon as I click the button. is your office 2007 up to date with all service packs and updates? (I don't know about access but excel 2007 is worthless before sp2)

[jik_ff]

Got it working, if you see my edit above. Would like to get away from having the end user have to remember to allow the security (can you open the security on a server share? Will have to find that out later), but I don't think I can do what I want with expressions.

[JosephP]

can you make the database location a trusted folder for the users?

[jik_ff]

I can, but the admin staff cycle quite often, didn't want to have to administrate that for ever... ah well. Thanks again. It's working well enough to continue on.