The identifier [InventoryItem].[Status] could not be found?

[djspod]

Hi,



I'm learning from the book Pro Access Development (Building a fictional library database)

I have followed the books great instructions however the table macro has failed and I can't understand why.

I have double-checked the macro steps from the book and I believe I have entered the data correctly.

The error code is The identifier [InventoryItem].[Status] could not be found?

Can anyone help please as I would like to progress with my Access development using the book.

Thanks

[rpeare]

that's likely not the name of the field, it looks like you have captions created on your table (I'm looking at the last column that starts 'how many times has ...') you would have to reference the 'status' field by it's real name your screen shot does not show a 'status' field. Plus you are showing a screen shot of the LOAN table, but you are being prompted for the STATUS field on the INVENTORYITEM table. What are you trying to accomplish with this macro? This macro seems to have some conflicting commands or redundant checks as well.

[orange]

Further to rpeare's comments, it would be helpful if you could show the fields/table design for the InventoryItem table.

[djspod]

Thanks for the help guys

[djspod]

[djspod]

Full screenshot of the Status field properties

[djspod]

Hopefully the screen shots and book extract will explain better, Let me know if you need any more clarification.

[orange]

I see a field STATUS in your InventoryItem table. So, I don't know why it isn't being found???

[djspod]

https://drive.google.com/open?id=0B6...dDA&authuser=0

The Library DB if that helps

[orange]

Can't access your file without permission???
Also, I don't use macros.

[djspod]

Try again I have made the link unrestricted.

As for Macros I only know what the book as taught me so far.

Would it be easy for myself to learn VBA instead?

[orange]

Would it be easy for myself to learn VBA instead?

A very good question. There are many who know both and say that macros are too restrictive.
VBA can NOT be used with web access databases (and Sharepoint) based on several posts.

This seems to be a critical issue to many Access developers. There is a feeling their concerns have not been adequately addressed by M$oft. I'm retired. No new development, just some reviews/participation in forums.

Do some googling, ask lots of questions and then decide. If you are young and starting out, then you should not be limiting your exposure to either. Learn each as you can, look for options.

Good luck.

For vba, see Function X and/or Crystal's material

[orange]

I'm looking at your database.

What differentiates and Item from an InventoryItem? Some attribute/characteristic should clearly tell you that it is one or the other--can't be both. It isn't clear to me, but you know your set up better than anyone.

Using paper and pencil, make some test data and some test scenarios, then work them against the model. Look for any anomaly and reconcile whatever is causing an issue-- eg bad data, bad table design, wrong relationship...
Then readjust and retest until the mode supports your test (which should represent your business needs).

This sql shows me the status values in Inventoryitem table

SELECT InventoryItem.InventoryItemID
, InventoryItem.ItemID
, InventoryItem.Status
, InventoryItem.Condition
, InventoryItem.Comment
FROM InventoryItem;

with results

InventoryItemID
ItemID
Status
Condition
Comment

1
1
Available
New



2
2
Checked Out
Good



3
4
Available
Fair



4
3
Checked Out
Poor



5
1
Available
Good



6
4
Available
Poor



7
4
Checked Out
Good



8
3
Available
Good



9
2
Available
Good



10
1
Available
Fair



11
3
Available
Poor

DO NOT USE TableLookups AT THE TABLE FIELD LEVEL

Use traditional Lookup/Reference tables

see Evil Lookups in Table fields

[rpeare]

There is nothing in the database but tables, no macros to test etc but based on reading the the blurb in post #5, why would you check the availability when the database is trying to add a record, why wouldn't you just limit your combo box of titles to those that were available in the first place. Seems like placing the cart before the horse.

Why does your LOAN table have the InventoryItemID and not the ItemID, you are loaning a book, not the status. If you want to track the condition of the book over time to a particular person and or loaning event you can keep inventoryitemID in your loan table but you really don't need the status in the invenotryitem table at all, you can figure the status based on the dates you have in your LOAN table

Example:
A loan that has a checked out date but no checked in date is 'checked out'
A book that has no records with a blank checked in date is 'available'

Your inventoryitem table shouldn't need the itemID just the LOANID so you can register the outgoing and incoming condition of the book on a particular loan.

I think this is just a case of not quite the structure you need.

[orange]

rpeare,

I wasn't checking the logic, but good call on your part.
I think the use of macros can not resolve the lookup field in the table.
Or, use of Lookup field(s) in table(s) is hidden from macro (untested in any detail)

orange