"Virtual" Forms And Accessing Data From A Specific Row

[PhaseEight]

Hello,

I am creating a research database and the two tables that are relevant to this question are called “Extract” and “Category”. These tables have a many-to-many relationship to one another. Please see the attachment “Relationship” for an idea of the structure for these two tables.
I have created a form (as shown in the attachment ExtractForm) which allows the user to input extract records into the system and link each extract to one or more categories. The main form is based on the Extract table and the subform is based on the join to the category table.


Based on this setup, I have the following questions:

1. I want to prevent the user from adding a record to the extract table unless there is at least one category attached to it. Unfortunately, this seems impossible since the operator cannot even begin to enter category codes until an extract record has been created. Is there any way to create a form which does not directly update data in the tables until ALL data on the form can be validated according to the developer’s rules? I would have no problem updating the data in the tables manually via VB code myself if there was a way to setup the form as shown in the ExtractForm attachment above. In this case I need the concept of a virtual or temporary form which stores the field data in memory and then, via a button click, allows me to update the fields once my validation has been performed. The problem is that I do not know if there is such a concept in Access.

2. When I want to access various fields from within VB I can do so by referencing the field name. For example, if I want to access the ArticleId field I just refer to it by its label and, in this case, the value would be “6”. Is there a way to access specific values in the subform’s fields? For example, in the screenshot shown above there is the Category subform with the two fields CategoryId and Description. The CategoryId field contains the values 1,2,3 and 4. How do I access the value, for example, of the CategoryId field on the second row (which, in this case, would be “2”)? Please note that in this case I am trying to avoid having to go to the table itself and use SQL commands to extract this data. I thought that since that data was on screen there must be a simple way of accessing these values as though they were in memory.

I apologize if this question displays ignorance to VB and Access. I developed commercial database software for 10 years in Unidata and System Builder. This environment allowed the developer to store delimited data in records without utilizing joins. In other words, the record structure was not flat. It also allowed one to create forms that were not necessarily directly linked to data in any particular table. I am having relearn the fundamentals with MS Access as the structure is completely different and much more “flat”.

I appreciate any help anyone can give me, even if it is a “no, you can’t do that in Access”. Either way at least I will have closure on this LOL.

Thanks.

[slave138]

1. If you look at the left side of your form in the screenshot, you might notice the pencil graphic near the top? When that is shown it means the record is in the process of being created but has not been committed to the table(s) yet.

I truly loathe dealing with listboxes so I can't help much when it comes to them but you should be able to verify the selection is not Null in the BeforeUpdate event and block it from saving if required selections have not been made yet.

2. You can refer to objects on a subform with code like:

Me.sfrmSubform.Form.ComponentName

This could be of help when dealing with subforms:
http://www.mvps.org/access/forms/frm0031.htm

[PhaseEight]

Thank you for your response.

I did not notice that pencil icon in the top left corner before. At least now I know what it means.

As for referring to a specific object in a subform, my problem comes in in that object holds more than one value (as is the case in the category subform). How would I reference a specific row? When I refer to the "Category" field it seems to return the value from the first row. I though there might be a way to specify the specific row which I want to interrogate.

Thanks again for your reply.

[slave138]

Your subform appears to be a table or query output -- if so, I don't think you can select items from it and have it saved with a record.

You would probably be better served by a combo box (if only one category can be selected per record) or a listbox (if you want to allow multiple selections) -- either way, you could put them on the main form instead of having to deal with a subform.