Full Version: Macro Error While Loading Form!
UtterAccess Forums > Microsoft® Access > Access Forms
Hi Gurus,

I am facing this weird error while I am trying to load my form (frmPRO). Failing at a IF condition. It would be a great help if someone can help me solve this error.

Error screenshots are posted below and Sample DB is attached in ZIP file.

Click to view attachment

Click to view attachment

Sample DB attached.
Click to view attachment

Also it is very helpful if could look and suggest me any changes in the DB design / relationships.

Thanks to you in advance.

Hi Ravi

Had a quick look at your example and not really following your table structure??

What have you 3 tables with the same structure?? ie tblPECP, tblZQ41 anf tblDEV

Why have you 1 to 1 joins set from tblPRO to all other tables??

A more detailed explanation of your business process might help??
Hi Mike,

Thanks for looking into my template. Please note the process as below:
1. Each table instructs ONE module for PRO
2. tblPRO is the main table into which all the orders are stored
3. Each PRO has to go through 8 stages of clearance (PECP, ZQ41.... etc,)
4. frmPRO is the main form which deals with individual PROs showing their status (based on selection PECP if PECP button is selected, ZQ41 if ZQ41 button selected).
5. When the buttons are clicked it fetches the respective Module details into the SubForm (objSubfrm)
6. Each Module has status (Complete, Pending). If completed, the respective button will be disabled.
7. Once all the status are Completed, the tblPRO.PROStatus will be set to completed and will disappear from the list.

I thought this will be a better design to navigate to PROs easily. This form will be updated by query which filters PROs based on the requirement of the User (PECP completed, ZQ41 completed, LIMS Pending etc,,,).

This is the first time I am working with Access interface, if there is any better alternative to achieve the above goals, please let me know. Do let me know if I need to provide any more clarity.

PS: This application is targeted to be used by 1 user for most of the time. Occasionally, another user and the Team Lead might also access it at the same time.

Thanks again for looking into this.

Hi Ravi

You say "tblPRO is the main table into which all the orders are stored"

Does the user enter data manually for the fields in tblPRO or is this fata being imported from another system??
No this is not good design. You are using your table/field names to identify data. Instead you should have ONE table with a field to identify Type of clearance each order goes through.

As to your error. You are comparing a controlname name PRO_Status but you don't have a control named Pro_Status on your form.

What I think you want to do is have one table then have a combobox on your form that filters the subform for the stage.
The others have pointed out that the table design is flawed, so I won't address that.

I added the missing field to the form's controls, as Scott pointed out, but that didn't seem to clear the error right away for me.

However, after I played around with the macro a bit, altering and saving it, the error went away.

That makes me think this was a temporary problem in the way the macro was compiled.... Try altering and saving it in your copy. Let me know if that helps.

And do pay attention to the suggestions about table design.
Hi All,
Many thanks for looking into my issue and helping me understand better design.

Yes the data is being populated from SAP using SAP Script. When a new Order number (PRO) is added to the tblPRO, I am adding the same PRO to rest of the tables which process the clearance modules.

I too am not satisfied on how the status check on the form is being tackled. Unfortunately having ONE field cannot justify the status of the Order. Clearance need not necessarily be in the same order as displayed in the forms. Depends on how long each department takes and how they prioritize their orders. Sometimes the first 2 and the last modules will be cleared, though one of the middle ones will be the last to be completed. Any other suggestion to tackle this?

Will try your suggestion. Though I thing that might recur the problem sometime later at the End User.

Reg Issue: Actually if the error is being reproduced due to a missing control, it might help me to avoid it completely by adding a field. I need a status update on the main form when each PRO is opened.
Reg Design: I now understand the design itself is flawed. I would like to get it right before I proceed. Do send me your suggestion on Best Practice to tackle this type of database.

Thank you all,

Hi Ravi

OK so your tblPro is obtained from another source.

How many records are appended to tblPRO from SAP at any one time??
Hi Mike,

I am fetching the data from SQL Server. I am using ADODB.Recordset.

I am looping through the Recordset, processing the record and adding it to the tblPRO. I am appending the PRO to rest of the Tables at the same time.

DownloadPRO module in VBA: FetchPRO

Thank you,

Can you show us the code in FetchPRO?

You should be able to populate a field in a single table with the type.

Sometimes the first 2 and the last modules will be cleared, though one of the middle ones will be the last to be completed. Any other suggestion to tackle this?

Ok, so you need to identify which stages have been completed, is that the issue? There are two ways I would handle this. I would use a Stages table:

ProStageID (PK Autonumber)
StageID (FK)

You would also need a table of the stage names with an autonumber StageID as PK.

So when you create the Main PRO record, you can automatically populate the above table with all the stages. Then use a subform on your mainform to enter the dates each stage was completed.

The other option would be to use a MVF and just check off the completed stages, but that doesn't allow you to record WHEN the stage was completed.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.