From: Jeanette Cunningham on
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" <PHisaw(a)discussions.microsoft.com> wrote in message
news:91DE8C4F-823C-424C-9CF8-26B6D5A165B4(a)microsoft.com...
> 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" wrote:
>
>> 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" <PHisaw(a)discussions.microsoft.com> wrote in message
>> news:7B242E41-14D9-45A6-B7B0-6FEAADAF5D26(a)microsoft.com...
>> > 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" wrote:
>> >
>> >> 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" <PHisaw(a)discussions.microsoft.com> wrote in message
>> >> news:A3D50D88-1C4B-4F45-90B9-F61BD7DF1C7F(a)microsoft.com...
>> >> > 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
>> >> >
>> >> >
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>>


From: PHisaw on
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

"Jeanette Cunningham" wrote:

> 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" <PHisaw(a)discussions.microsoft.com> wrote in message
> news:91DE8C4F-823C-424C-9CF8-26B6D5A165B4(a)microsoft.com...
> > 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" wrote:
> >
> >> 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" <PHisaw(a)discussions.microsoft.com> wrote in message
> >> news:7B242E41-14D9-45A6-B7B0-6FEAADAF5D26(a)microsoft.com...
> >> > 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" wrote:
> >> >
> >> >> 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" <PHisaw(a)discussions.microsoft.com> wrote in message
> >> >> news:A3D50D88-1C4B-4F45-90B9-F61BD7DF1C7F(a)microsoft.com...
> >> >> > 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
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >> .
> >> >>
> >>
> >>
> >> .
> >>
>
>
> .
>