PopUp Form Help

[PHisaw]

I'm trying to create a pop-up form to allow users to enter data to table that
main form is based on. Due to size of main form and because pop-up fields
will only be used per certain entries on main form, I want to use a pop-up
form to enter needed data. On the main form, I have the fields listed below.
For example, if user enters "TypeOne" as a PType and then enters Model,
AfterUpdate to Model field a pop-up form opens with fields for UnitGroupName
and UnitModelName. I would like the UnitGroupName to be populated with "All
TypeOne Products" when the pop-up opens and then select UnitModelName from a
combo filtered on entry in UnitGroupName.


Main Form fRepairs
JobNumber (PK)
PType
Model

On AfterUpdate event for model :

Private Sub Model_AfterUpdate()
If Me.PumpType = "TestOne" Then
DoCmd.OpenForm "fGroupInfoForStatusCountCalculated", , , "JobNumber=" &
Me.JobNumber
End If
End Sub

On form "fGroupInfoForStatusCountCalculated":

JobNumber
GroupUnitName
GroupModelName

Private Sub Form_Open(Cancel As Integer)
If Forms!frepairs!PumpType = "TestOne" Then
Me.UnitGroupName = "All TestOne Products"
End If

When I enter a model to the Model field, it throws an error "you can't
assign a value to this object" and on Debug this row is highlighted:

Me.UnitGroupName = "All TestOne Products"

If anyone would please tell me what I'm doing wrong, I would greatly
appreciate it. I also have six PTypes to set up this way. Once I get code
in place, would it be better to use multiple "if" statements or "case select"
statements - I'm never sure which to use in situations like this.

Thanks in advance for any help.
Pam

 

[Jeanette Cunningham]

Often, the Open event of a form is too early to enter data. Try using the
Load event of the popup form to run this code

Private Sub Form_Load()
If Forms!frepairs!PumpType = "TestOne" Then
Me.UnitGroupName = "All TestOne Products"
End If

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[PHisaw]

Jeanette,

Thank you for responding to my question. I did as you suggested and the
form is opening as it should, I'm able to fill in data, but when I leave the
form - AfterUpdate event: DoCmd.Close - I get a Write Conflict error: Record
changed by another user since you started editing it...

I'm using the fields from the same query that the main form is based on. If
this is the problem, what would be the most efficient way to correct it?

I certainly appreciate your time and help.
Pam

 

[Jeanette Cunningham]

The write conflict error comes up when you have 2 forms based on the same
table both open for editing at the same time.
If you hide the main form after opening the popup form, it should work.
You could probably just make the main form's allow edits, allow additions
and allow delete to No, right before you open the popup and change it back
when you close the popup, instead of hiding the main form.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[PHisaw]

Jeanette,

Thanks for responding after the long delay (weekend, holiday, other
projects...). While waiting for a response, I created a new table with just
the fields for ID, JobNumber, UnitGroupName, ModelGroupName. I based the
pop-up on this table and used OnLoad instead of OnOpen and it seems to work
well. If I need to view all the job info at once, I can join tables with
JobNumber.

Is it better to have a separate table for instances such as this or does it
cause unnecessary database bloat? I'm redesigning a major database and want
to streamline it as much as possible?

Thanks for your help.
Pam

 

[Jeanette Cunningham]

When designing tables for a database, forget about forms and concentrate on
the real world things that the database is dealing with.
There are many articles on normalization for a database on the internet,
read up on some of them - having a normalized table structure is the key to
a successful database.

For that problem with the popup that we have been discussing, the code we
discussed is a better alternative than creating a separate table to get
around the error, unless normalizing the database makes it mandatory to have
that extra table for ID, JobNumber, UnitGroupName, ModelGroupName.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[PHisaw]

Jeanette,

Thanks for the info. I will try your suggestions and see if I can get it to
work without the additional table.

I appreciate your help.
Pam