Navigation Form (1 Viewer)

[Rmaster2022]

I am using a Navigation Form. One of the subforms is frmOcupation. On that form is a button which opens a popup form, popupOccupationAdd, in which I can add a new record. I have a where expression that automatically inserts the memberID from frmOccupation. I enter the remaining information manually. That is working fine.

I want to be able to requery, close popupOccupationAdd, and return to the same person’s record in frmOccupation. Do I use “browse to” to get back to frmOccupation? I am having trouble writing a where expression so that when I close the popup frmOccupationsAdd I will return to the same person’s frmOccupation record.

 

[theDBguy]

Hi. It's quite possible BrowseTo is what you need, but it might help figure out the where expression if you could post a sample db.

 

[Pat Hartman]

Are you saying that you have separate forms to view and add the same data?

 

[Rmaster2022]

Are you saying that you have separate forms to view and add the same data?

Yeas

 

[Pat Hartman]

Generally we use ONE form to perform all operations. Occasionally, you might have slightly different rules for update vs add but you can handle those rare situations by using If Me.NewRecord to distinguish an update from an add.

You may of course do whatever you want to do but there is no benefit to using multiple forms to interact with the same table and multiple negatives.

I think my confusion with your question has to do with the form names. frmOccupation seems to have a PK of MemberID. It isn't clear to me if you are using the popup form to add a new occupation to the list of occupations or to add a new occupation for a member. I would expect frmOccupation to be the form that manages the definition of occupations but has nothing to do with a member. But having MemberID as the PK makes me think that perhaps the form name should be something else. Also, if a member might have multiple occupations, that would be better managed using a subform rather than a popup.

I'm with theDBguy, I think we need to see more to understand the problem.

 

[Rmaster2022]

Sorry to be slow, but I have finally reworked my database based on your recommendations to simplify things and reduced the forms.

The help I need is in the form ‘Occupations’ under the Pastors tab. There are three subforms, one under the Current heading, another under History, and another under Add. The criteria for the query for the Current subform is based on the EndDate field, “Is Null or >Date()” The criteria for History is “<Date()” What I want to do is to have the History or Current subforms requery (and/or refresh?) after adding a record and clicking on “Save Record” within the Add subform.

One other item is the church combo boxes in all three subforms. When a congregation is dissolved, I want a conditional format so that the font is red for a church that is dissolved. I have done these, but evidently cannot get the expression right. Dissolved is indicated by placing a date in the Dissolved field on the Congregations form.

I have attached the file with some records.

 

[Pat Hartman]

I'm not sure you understood any of what I suggested. The form you are referencing doesn't have any "tabs". It does have three subforms bound to the same table. I can't figure out what table the forms are supposed to be maintaining. And you still have add separate from update. It could be tblOccMmbr but the forms only contain four fields and so one very important field is missing - MemberID.

I don't see any reason for any of the other tables/queries to be part of the recordsource. To handle "disolved", add the DisolvedDT to the ChurchID Combo RowSource.

If you want to see history rather than current, just add a filter to the main form.

You also have no validation on any form. Therefore you can save empty or incomplete records.

Because you still have three separate forms, you have three places to write code.

Other issues.
1. names with special characters
2. PK named ID
3. No relationship defined - some due to bad data existing in tables, some due to other reasons.

 

[Rmaster2022]

Yes, I didn’t understand, and I still may not. It is obvious that I am not an expert in ACCESS, nor do I claim to be competent in ACCESS.

I changed the Occupation form to not have 3 subforms. Any records that are “History” I added a conditional format.

I think I removed the special characters.

Is it OK to have “ID” included in the PK name, not be solely “ID”? Example: Candidate ID

I validated fields within the tables.

I don’t understand what you mean by the relationship comment. I do have 2 tables that are not related, for a good reason.

Are you willing to take a quick look at my database design again? I’d like to privately send you what I have now. I prefer that you review with my real fields, and so I’d rather not make it public.

 

[Pat Hartman]

The problem with taking the conversation off line is that now I am working for you alone and not being compensated. I spend a lot of time helping people here and don't expect to be paid because hundreds or even thousands of people can benefit from my "sage" advice. Even my husband. He was also in IT and occasionally had to help a user with an Access problem. But, since Access wasn't his normal environment, he would frequently need to search for the answer and often came up with one from me. We got a kick out of it.

It is important to have rational data in a database if you want someone to evaluate it. Random values in fields make it difficult. So, the best solution is to obfuscate the identifying information. Make sure to make a backup. You don't want to accidentally clobber your real data. I zip the data. Then I copy the BE and zip the copy so I have two copies of the BE. Then I work with the copy. I update LastName, Address, phone, SSN, email, etc. Things that are unique to you like CustomerNumber don't generally need to be obfuscated unless you tell us what your company name is and maybe not even if you do.

I run a query that takes the first and last letters and updates the field to that so Hartman becomes Hn, 101 Main St becomes 1t, etc.