Need more help!

[Database Challenged]

So, I'm a little further along in my updating/modification of my database. We paid someone last year to do some VBA coding for us and - to be honest - I'm clueless as to how it works. (But we have no money to get someone to do all THIS work so....I'm doing it.)

Anyway, I'm getting an error that says:

tblResources is a linked table with some properties that can’t be modified.

Anyone have any idea what this means? It's in English but it might as well be in Swahili. I did some modifications to the back end to a field that should have been a lookup but kept coming up as a yes/no, despite the fact I was inputting everything AS a lookup. So I deleted that field and re-formatted it/input it/whatever you want to call it and now the front end is harassing me!


Thoughts?




Ok, I went back in. It seems the changes I made are sticking but the data in the back end is not being "linked" to the data in the front end. I got this error.

Private Sub Active_Title_AfterUpdate()
On Error GoTo ErrHandler

If MsgBox("Make " & Me.Parent.lboResSelect.Column(1) & " inactive?", vbOKCancel, "Inactive Confirmation") = vbOK Then
DoCmd.Save
Forms!frmARM!lboResSelect.SetFocus
Forms!frmARM!lboResSelect.Requery
Forms!frmARM!txtResSelLstCount.Value = Forms!frmARM!lboResSelect.ListCount
Forms!frmARM!lboResSelect.Value = 0
Forms!frmARM!lboCompList.RowSource = "SELECT tblComponents.ComponentID, tblLnkResComp.ResourceID, " _
& "tblComponents.Description, tblComponents.DateofExecution " _
& "FROM tblComponents INNER JOIN tblLnkResComp ON tblComponents.ComponentID = tblLnkResComp.ComponentID " _
& "WHERE (((tblLnkResComp.ResourceID) = 0)) " _
& "ORDER BY tblComponents.Description;"
Forms!frmARM!txtCompListCount.Value = Forms!frmARM!lboCompList.ListCount
Forms!frmARM!TabCtlResource.Visible = False
Forms!frmARM!TabCtlComponents.Visible = False
Else
Me.ActiveTitle.Value = 1
End If

ExitSub:
Exit Sub
ErrHandler:
If Err.Number <> 2046 Then
MsgBox "Error in Active_Title_AfterUpdate: " & Err.Number & " - " & Err.Description
End If
Resume ExitSub
End Sub

 

[MSAccessRookie]

Regarding the data not updating, the message "tblResources is a linked table with some properties that can’t be modified" can mean the following:

• You are attempting to modify a Table in the Front End of a Split Database in a way that is not permitted (Ex: Adding, Modifying, or Removing a Column).
• You have modified a Table in the Back End of a Split Database, and have not updated the Front End to reflect the changes yet.

You will need to modify your Tables in the Back End of a Split Database only, and then Refresh the Links in the Front End once the Modifications have been completed before you are able to use them successfully.

If neither of these issues applies, please post back and we can try to explore additional options.

 

[Database Challenged]

Ok, I see what you're saying and I think I've done that correctly and the issue might be VBA - or my complete inadequateness. Below is more detail.

To explain, in a way I hope you can understand, I wanted to change the ActiveTitle field (a yes/no check box) to AssetStatus (a lookup with 3 choices) on tblResources. I did this first in the back end then updated the front end. I deleted AT and inserted AS in its place. The AT field is used in queries and some VBA coding. I found the queries and tried to modify those to pull the AS instead but the coding - which I don't understand - I don't know how to change. It throws up an error obviously becuase required fields are now gone. So I thought I changed it back.

When I look at the database now, both the back end and front end tables with these fields have the ActiveTitle field AND the AssetStatus field, which I thought would be ok as just an added field. Despite this field being put back and the tables being relinked, the data is still not appearing in the front end. It says there are zero records when I know there are 519.

I hope this is clear. Please feel free to ask any questions. I so very much appreciate all your assistance. I'm quite frustrated and in need of some chocolate. STAT!

 

[MSAccessRookie]

Ok, I see what you're saying and I think I've done that correctly and the issue might be VBA - or my complete inadequateness. Below is more detail.

To explain, in a way I hope you can understand, I wanted to change the ActiveTitle field (a yes/no check box) to AssetStatus (a lookup with 3 choices) on tblResources. I did this first in the back end then updated the front end. I deleted AT and inserted AS in its place. The AT field is used in queries and some VBA coding. I found the queries and tried to modify those to pull the AS instead but the coding - which I don't understand - I don't know how to change. It throws up an error obviously becuase required fields are now gone. So I thought I changed it back.

When I look at the database now, both the back end and front end tables with these fields have the ActiveTitle field AND the AssetStatus field, which I thought would be ok as just an added field. Despite this field being put back and the tables being relinked, the data is still not appearing in the front end. It says there are zero records when I know there are 519.

I hope this is clear. Please feel free to ask any questions. I so very much appreciate all your assistance. I'm quite frustrated and in need of some chocolate. STAT!

I hope you found enough chocolate, because I have a few more comments/questions.

• The VBA Code that you provided makes no reference to a Table called tblResources. Please elaborate on this requirement.
• The VBA Code that you provided makes no reference to a Column or Form Control named AssetStatus.
• The VBA Code that you provided makes no reference to a Column named ActiveTitle, although there is a reference to a Form Control with that name.

I think that there is some information missing.

 

[Database Challenged]

Thank you for your help! Unfortunately, I don't think there is enough chocolate in the world to help me today.

Let me take your three points one by one:

1. I was just assuming the VBA code needed something from the tblResources. I didn't do the VBA programming so I have no clue that it really is a requirement but I assume it is. If it's not stated in the code I provided, it's probably in code somewhere else in the database for another function.

2. Yes, the code doesn't reference AssetStatus because it's a new field I want to add - post all this coding done last year. I did not put this field anywhere else.

3. Again, I don't understand enough about VBA to tell you why this is. I'm sure it has to do with how the guy coded everything in the first place. Things are tied together to make other functions work.

I think you're right about the missing information. It's missing from my brain and abilities!! Since I'm tasked with doing this, it's imperative I get it right.

Is there any quick VBA tutorial/training online I can take/read that would help me with this? I can't even read and understand the language which is a major roadblock.

 

[MSAccessRookie]

Thank you for your help! Unfortunately, I don't think there is enough chocolate in the world to help me today.

Let me take your three points one by one:

1. I was just assuming the VBA code needed something from the tblResources. I didn't do the VBA programming so I have no clue that it really is a requirement but I assume it is. If it's not stated in the code I provided, it's probably in code somewhere else in the database for another function.

2. Yes, the code doesn't reference AssetStatus because it's a new field I want to add - post all this coding done last year. I did not put this field anywhere else.

3. Again, I don't understand enough about VBA to tell you why this is. I'm sure it has to do with how the guy coded everything in the first place. Things are tied together to make other functions work.

I think you're right about the missing information. It's missing from my brain and abilities!! Since I'm tasked with doing this, it's imperative I get it right.

Is there any quick VBA tutorial/training online I can take/read that would help me with this? I can't even read and understand the language which is a major roadblock.

Try one of these (perhaps others have additional suggestions).

http://www.functionx.com/vbaccess/index.htm

http://allenbrowne.com/tips.html

 

[Database Challenged]

Thanks! I will try those. I've also got a book on VB on hold at Barnes & Noble. I'll pick it up after work!