2 issues - uable to enter data and how to check for open forms

[RexesOperator]

Part 1: Most everything on this form works the way I want it to, except for one minor detail - I can't enter any data in any field except for the CompanyID field! Could someone take a look and see if they can spot anything?

(Yes I know my PKs are text fields - I have a reason for this.)

Part 2: How can I check programatically to see if there are any open forms? Some may be hidden underneath popup forms that I am using.

 

[boblarson]

Part 1: Most everything on this form works the way I want it to, except for one minor detail - I can't enter any data in any field except for the CompanyID field! Could someone take a look and see if they can spot anything?

(Yes I know my PKs are text fields - I have a reason for this.)

What is the reason? You really should just use autonumber PK fields and let ACCESS manage the primary keys. You can put an index on the other fields so that you don't get duplicates. Trying to do PK's this way is not a very good thing to do.

Part 2: How can I check programatically to see if there are any open forms? Some may be hidden underneath popup forms that I am using.

CurrentProject.AllForms.IsLoaded(FormNameAsStringHere)

Or if you just want to iterate through all open forms:

Dim frm As Form

For Each frm In Forms
  If CurrentProject.AllForms.IsLoaded(frm.Name) Then
     MsgBox frm.Name & " is open"
  End If
Next frm

 

[RexesOperator]

What is the reason? You really should just use autonumber PK fields and let ACCESS manage the primary keys. You can put an index on the other fields so that you don't get duplicates. Trying to do PK's this way is not a very good thing to do.

I am combining data with an old dbIII project that had an entirely different structure to it. All the ID fields were text fields. This way (and with some other modifications) I can get the original data into my forms.

But this shouldn't affect the form's behaviour. Why can I enter data into one field (CompanyID), but not the others?

Thanks for the procedure on checking for forms - I will check it out in the morning.

 

[boblarson]

I am combining data with an old dbIII project that had an entirely different structure to it. All the ID fields were text fields. This way (and with some other modifications) I can get the original data into my forms.

There's no reason why you can't change to autonumber PK's with the other data. Just because one system did it that way, doesn't mean it needs to stay that way. The problem, for one, is that nothing should be using the PK's for anything, except the system. So, it is reasonable to expect the system to maintain the PK's. Text PK's are not as efficient as numeric keys.

So, anyway, I'll take a look (this is all before I even took a look at it) and try to give you some info on how to convert as well as why your form is having problems.

 

[boblarson]

Okay, the reason why you have a problem with your form is that your query is not fully updateable due to many one-to-many relationships. You need to use SEPARATE forms (subforms) for the many side of the relationships.

 

[RexesOperator]

I realize two wrongs don't make a right. Eventually everything will be converted to autonumbers. My immediate task is to get this up and running before I retire April 1st.

BTW I did a demo of the stuff that is completed for my manager and I think I blew him away. His expertize (sp?) is Excel. He is familiar with VBA but has never used Access. His reaction was "Wow!" Not bad for a complete neophyte! I gave full thanks to the forum - it will be part of the documentation.

 

[RexesOperator]

Okay, the reason why you have a problem with your form is that your query is not fully updateable due to many one-to-many relationships. You need to use SEPARATE forms (subforms) for the many side of the relationships.

Perhaps the appearance of the tabs is misleading - the two forms Qe and NBCC ARE subforms. They call the same query, and they have overlapping information, but they also have some information that is unique to both forms. Do I have to use separate forms for them then?

 

[boblarson]

Perhaps the appearance of the tabs is misleading - the two forms Qe and NBCC ARE subforms.

I didn't even look at that. I'm talking about your query qryTransactions which is the main recordsource of the main form. It is not fully updateable because you have a whole bunch of tables linked in it and they are one-to-many relationships.

 

[RexesOperator]

What do you mean by a whole bunch? There are five tables: Company->Contacts->Transactions->Sites and ECEmployeeInfo->Transactions. These are all simple 1 to many relationships. I need some details from each table in order complete a transaction. What have I missed?

 

[boblarson]

What have I missed?

What you've missed is that you can't update a ONE side when you are linked to several tables with a MANY to more than the one table. So, you should remove these tables in your main query:

tblSites
tblContacts
tblECEmploymentInfo
tblCompanies

You don't need them in your query. You will use combo boxes to select from those items and store their primary keys, as foreign keys, in the tblTransactions.

 

[RexesOperator]

OK I will have to take a look at this tomorrow - thanks for your guidance.

 

[RexesOperator]

Finally got it working - thanks Bob.