Update Problem

D

Derek Brown

Hi all

How do I force a form to save after programatically entering data, without
entering the form.
 
R

Rick Brandt

Derek said:
Hi again

Can I use you reply to solve the following

I have an Invoicing form that consists of 1.main form, (customers
details) 2. Subform Invoice details (invoice number delivery address etc. and
3. Sub-subform, Invoice items.(Products prices etc).

The Problem. The form has three tables one for each form linked with
referencial integrety. When a customer calls, the user finds the
customers details. The user usualy tries to skip entering details in
the first subform (2) and tries to enter information straight into
the item subform (3) without entering information into the first
subform (2)causing a gap in the link between the three tables. Is it
possible to automatically enter a value linking the field between (1)
and (2) so that they can go straight to the Item or SuSubform (3)
without causing a referencial integrity error? "Rick Brandt"
Click to expand...

What I usually do is hide or disable the inner form until after the user has
made an entry in the outer form.
 
D

Derek Brown

Hi again

Can I use you reply to solve the following

I have an Invoicing form that consists of 1.main form, (customers details)
2. Subform Invoice details (invoice number delivery address etc. and 3.
Sub-subform, Invoice items.(Products prices etc).

The Problem. The form has three tables one for each form linked with
referencial integrety. When a customer calls, the user finds the customers
details. The user usualy tries to skip entering details in the first
subform (2) and tries to enter information straight into the item subform
(3) without entering information into the first subform (2)causing a gap in
the link between the three tables. Is it possible to automatically enter a
value linking the field between (1) and (2) so that they can go straight to
the Item or SuSubform (3) without causing a referencial integrity error?
 
D

Derek Brown

Thats great Rick

But how do you overcome the problem of wanting a form to open and show the
inner subform if data exists but not if no data is available.
I have tried:

Me!SubsubForm(3).Visible =Me!SubsubForm(3).HasData Doesn't work.
It's the sintax of where and how to use it in a three layer form.
I have also used the expression builder to no avail, any clues?
 
R

Rick Brandt

Derek said:
Thats great Rick

But how do you overcome the problem of wanting a form to open and
show the inner subform if data exists but not if no data is available.
I have tried:
Click to expand...

In the Current event of the outer form...

Me.SubformControlName.Visible = Not Me.NewRecord

In the AfterInsert event...

Me.SubformControl.Visible = True
 
D

Derek Brown

Thanks again

Should the code be writen in the first subform or the main form. I have
guessed it was the outer subform but try as i may i cannot get it to work. I
have Main Form, SubForm and SubsubForm
 
R

Rick Brandt

Derek said:
Thanks again

Should the code be writen in the first subform or the main form. I
have guessed it was the outer subform but try as i may i cannot get
it to work. I have Main Form, SubForm and SubsubForm
Click to expand...

In the first subform. Post your exact code.
 
D

Derek Brown

Hi Rick

Private Sub Form_Current()
Me.ItemForm.Visible = Not Me.NewRecord
End Sub

Private Sub Form_AfterInsert()
Me.ItemForm.Visible = True
End Sub
 
R

Rick Brandt

Derek said:
Hi Rick

Private Sub Form_Current()
Me.ItemForm.Visible = Not Me.NewRecord
End Sub

Private Sub Form_AfterInsert()
Me.ItemForm.Visible = True
End Sub
Click to expand...

Is ItemForm the name of the subform *control* or of the form within? You need
to use the name of the control.
 
D

Derek Brown

Hi Rick

I have a form called ItemForm that i inserted into the outer Subform which
is called Invoice Form. Is this not the control? In the Module I have code
that is writen in the ItemForm and the title of the code (or the container?)
is Form_ItemForm. So I assume that this is the control name. Is this
correct?

Thanks Rick
 
D

Derek Brown

Hi Rick

Thought I would let you know that I have solved the problem (with your help)
Although I wish I understood what I did. I put this in the lower sub or the
inside sub
Works great but why would Me.Visible Work in the inner sub when
Me.ItemForm.Visible would not in the higher subform?
 
R

Rick Brandt

Derek said:
Hi Rick

Thought I would let you know that I have solved the problem (with
your help) Although I wish I understood what I did. I put this in the
lower sub or the inside sub

Works great but why would Me.Visible Work in the inner sub when
Me.ItemForm.Visible would not in the higher subform?
Click to expand...

That code will make it impossible for you to ever add a new record in the last
subform. Did you see my last message about using the name of the subform
control rather than the name of the form within it?
 
D

Derek Brown

Hi Rick

Perhaps some of the newsgroup postings are not comming through at the
correct time. I sent a post before the last one that answered your post here
it is again.

Hi Rick

I have a form called ItemForm that i inserted into the outer Subform which
is called Invoice Form. Is this not the control? In the Module I have code
that is writen in the ItemForm and the title of the code (or the container?)
is Form_ItemForm. So I assume that this is the control name. Is this
correct?

Does that help?

Thanks Rick
 
R

Rick Brandt

Derek said:
Hi Rick

Perhaps some of the newsgroup postings are not comming through at the
correct time. I sent a post before the last one that answered your
post here it is again.

Hi Rick

I have a form called ItemForm that i inserted into the outer Subform
which is called Invoice Form. Is this not the control? In the Module
I have code that is writen in the ItemForm and the title of the code
(or the container?) is Form_ItemForm. So I assume that this is the
control name. Is this correct?

Does that help?
Click to expand...

The newer versions of Access make this more confusing by showing the design view
of subforms at the same time you are viewing the design of the parent form.
Basically if you open the parent form in design view and click ONCE on the
subform then you will be setting focus to the subform *control* and its name
will be displayed in the property sheet. If you click again on the subform the
property sheet will change to displaying the properties of the form embedded
within the subform control.

You want to change the visibility of the control, not the form. Often the name
of both is the same, but you can't be certain of that unless you check it.
 
D

Derek Brown

Hi Rick

You were correct that code did not allow data entry. This does but still
seems a crude way to do it:
Into lowest subform I put:
Private Sub Form_Current()
If IsNull(Forms!MainForm!InvoiceForm.Form!AccountCode) = False Then
Me.Visible = True
Else
Me.Visible = False
End If
End Sub
 
R

Rick Brandt

Derek said:
Hi Rick

You were correct that code did not allow data entry. This does but
still seems a crude way to do it:
Into lowest subform I put:
Private Sub Form_Current()
If IsNull(Forms!MainForm!InvoiceForm.Form!AccountCode) = False Then
Me.Visible = True
Else
Me.Visible = False
End If
End Sub
Click to expand...

The problem with using the current event of the subform to hide "itself" is that
once the event triggers a "visible = false" then you have no ability to move to
another record to undo that since once the form is hidden you can no longer
navigate in it (it's a one way street).

I really can't think of any reason why the current event of the parent form
doesn't work for you. I have done this numerous times and it has never been an
issue.
 
D

Derek Brown

Thanks Rick

The reason I am getting away with this is because If no data exists in the
inner subform then I don't want the ability to make it visible. I want to
force the user to click a button on the outer subform which then enters a
few standard details (invoice number etc) and with the Dirty statement
causes the sub to save the record creating a link between the outer sub and
the main form and finishes with an instruction to make the inner subform
visible only after this event. Also the inner sub is in multirecord format.

I am just concerned that I never seem to find "the right way" I just make it
work, it fels like sellotape and chewing gum to me.! I will persivere with
the method you originally suggested until it works. One problem I found was
moving the focus to the inner sub, but I have taken that out and still have
problems.

Anyway it now works so thank you for your help!!
 
Top