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.