PopUp Form Help
in [Forms]
|
Prev: Final Reminder - Microsoft Responds to the Evolution of Community
Next: How do I use Access 2007 VBA to print external pdf files?
From: Jeanette Cunningham on 4 Jun 2010 18:24 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 7 Jun 2010 10:31
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 > >> >> > > >> >> > > >> >> > >> >> > >> >> . > >> >> > >> > >> > >> . > >> > > > . > |