By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,908 Members | 2,056 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,908 IT Pros & Developers. It's quick & easy.

Forms: Required Columns & Verifying Next Record

P: 1
I have a problem with verifying content of controls on a form before closing the form or moving to the next form "record" (i.e., when moving to the next row of my form's record source).


First, A Description of the Form:

I have developed a form for entering survey questionnaire data. The data to be entred on the form could be considered as two kinds of information.

One kind could be called "Response" information -- answers to survey questions. The controls for "Response" data are text boxes. I have no problems with them or their data.

The other kind of information I'll call "Identifying" information -- Starting Date of survey period; Ending Date of survey period; and Facility Code. The controls for "Identifying" data are combo boxes. My problem is with managing the content of these controls.

The row source for Starting Date is a list of known starting dates:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT T_Survey_Master.Starting_Date
The row source for Ending Date is determined by the value of Starting Date:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT T_Survey_Master.Ending_Date 
  2. FROM T_Survey_Master 
  3. WHERE (((T_Survey_Master.Starting_Date)
  4. =[Forms]![F_Survey_All_Entry].[Combo_Starting_Date]));
The row source for Facility Code is determined by Starting Date and Ending Date:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT T_Survey_Master.Facility_Code, T_Facility.Facility_Name 
  2. FROM T_Facility 
  3. LEFT JOIN T_Survey_Master 
  4. ON T_Facility.Facility_Code = T_Survey_Master.Facility_Code 
  5. WHERE (((T_Survey_Master.Starting_Date)
  6. =[Forms]![F_Survey_All_Entry].[Combo_Starting_Date]) 
  7. AND ((T_Survey_Master.Ending_Date)=[Forms]![F_Survey_All_Entry].[Combo_Ending_Date]));
Now, the Problem:

The Ending Date & Facility Code controls must be cleared if Starting Date is changed (because their ROW SOURCEs are determined by the value of Starting Date). If Ending Date is changed, the Facility Code control must be cleared (because its ROW SOURCE is determined by Starting Date and Ending Date) .

Since all three controls (both of the dates & the facility code) are REQUIRED, I can't clear them (can't set them to NULL). Okay, so I changed them into hidden text boxes (Visible = No) and added three new controls -- UNBOUND controls for Starting Date, Ending Date, and Facility Code.

VBA code runs on the form's OnCurrent event to set the UNBOUND controls = the values of the hidden BOUND controls. I set the UNBOUND controls to NULL any time I need to (based on Starting Date or Ending Date changing). Before INSERT or UPDATE events, I verify that these three unbound controls contain valid data. If they do have valid data, I update the hidden controls that are bound. If the hidden controls don't contain valid data, the user simply gets a dialog box with a warning & focus is set to the (UNBOUND) control where bad data was detected.

So now everything works great, except that if the user closes the form or uses a navigation button to go to another "page", the unbound controls could possible be blank. (The user may have cleared data from some of the UNBOUND controls, but not attempted an update.) That would present the user with a very goofy-looking interface. ("I can page off of this record with three blank fields, and everything is okay. Gee, I'm not sure I trust this application.")

I've tried a lot of things to beat this problem. I've seen only one post about a problem like mine the net, and the only solution offered was to replace the built-in navigation buttons with custom controls. That seems like a first step toward building a front end to the Access GUI, and I'm not up for that.

I'll welcome any suggestions.
Sep 27 '06 #1
Share this Question
Share on Google+
1 Reply

Expert 100+
P: 1,418
Can U do shorter? In fact do u have a problem with all that u do or with a small part of it?

Speek us about this small part... and a relative details! Really I can't concentrate myself to go in your shoes!
Sep 30 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.