Open new record subform for data entry

G

Guest

Have a form with a subform. On the main form have a List Box which when a PO
number is entried the PO and Vendor fields are populated on the Subform. The
Subform is based on the Invoice Table. But if there are invoices already
assoicated with this PO number in the Invoice Table, then the subform fields
are populated with data from that first invoice. What I want is to have the
subform open up in a NEW Record environment with the PO and Vendor fields
filled in.

Lin
 
S

Steve Schapel

Lin,

Does this meet your requirements?... Set the Data Entry property of the
form you use as the subform, to Yes.
 
S

Steve Schapel

Lin,

Is there a macro, or some code running on the Listbox?

Is the subform a single view or continuous view?
 
G

Guest

It is a single view
Under Properties for the box, all I see [Event Procedure] for "After Update".
Where else should I look.
The subform with acted on by itself, is a true data entry form. But in the
subform
condition I have had add a command key to open a new/blank invoice data
entry record.
Any hellp would be appreciated.

Lin
 
S

Steve Schapel

Lin,

Lin said:
Under Properties for the box, all I see [Event Procedure] for "After Update".

Yes, that's the one! Click the little ellipsis (...) button to the
right, to open the VBE window, and copy/paste the code into your reply.
 
G

Guest

This is from the List Box that is in the Main Form.

Private Sub Combo4_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PONumber] = " & Str(Me![Combo4])
Me.Bookmark = rs.Bookmark
End Sub

Lin

--
Lin Light Herrick District Library


Steve Schapel said:
Lin,

Lin said:
Under Properties for the box, all I see [Event Procedure] for "After Update".

Yes, that's the one! Click the little ellipsis (...) button to the
right, to open the VBE window, and copy/paste the code into your reply.
 
S

Steve Schapel

Lin,

Ah, ok, so the main form is bound to the PO table or some such, so you
have PO information on the main form? I didn't imagine this was what
you wre doing.

Well, the only thing I can think of doing at this stage is to explicitly
force the new record on the subform. So at the end of the code from the
Listbox (or is it really a combobox?), put the equivalent of this...

Me.NameOfYourSubform.SetFocus.
DoCmd.GoToRecord , , acNewRec
 
G

Guest

Where exactly at the end of coding. Under what event Enter, Afterupdate ?
Lin

--
Lin Light Herrick District Library


Steve Schapel said:
Lin,

Ah, ok, so the main form is bound to the PO table or some such, so you
have PO information on the main form? I didn't imagine this was what
you wre doing.

Well, the only thing I can think of doing at this stage is to explicitly
force the new record on the subform. So at the end of the code from the
Listbox (or is it really a combobox?), put the equivalent of this...

Me.NameOfYourSubform.SetFocus.
DoCmd.GoToRecord , , acNewRec

--
Steve Schapel, Microsoft Access MVP


Lin said:
This is from the List Box that is in the Main Form.

Private Sub Combo4_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PONumber] = " & Str(Me![Combo4])
Me.Bookmark = rs.Bookmark
End Sub

Lin
 
S

Steve Schapel

Lin,

Sorry, I meant at the and of the code you had already posted. E.g....
Private Sub Combo4_AfterUpdate()
Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[PONumber] = " & Str(Me![Combo4])
Me.Bookmark = rs.Bookmark
Me.NameOfYourSubform.SetFocus.
DoCmd.GoToRecord , , acNewRec
End Sub
 
G

Guest

Private Sub Combo4_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PoNumber] = " & Str(Me![Combo4])
Me.Bookmark = rs.Bookmark
Me.AVInvoicedataentrysubf.SetFocus.
DoCmd.GoToRecord , , acNewRec


This is what I have in the "Afterupdate" area. I changed the name of my
subform by removing all spaces. As soon as I try to run the form, I get the
Compile Syntix Error. and the line begining with Private is highlighted in
yellow and the line that I just put in is highlighted in blue.

Maybe there is a simpler way to do what I want. I want to enter in a PO#
and have the vendor assigned to that PO Populate the Vendor Field in my
Invoice Form so confirm for the data entry person that this Vendor does in
fact belong to this PO.
Then once that is confirmed, the data entry folks can then fill in the rest
of the form with Inv # and date, etc.
Lin Light


--
Lin Light Herrick District Library


Steve Schapel said:
Lin,

Sorry, I meant at the and of the code you had already posted. E.g....
Private Sub Combo4_AfterUpdate()
Dim rs As Recordset
Set rs = Me.RecordsetClone
rs.FindFirst "[PONumber] = " & Str(Me![Combo4])
Me.Bookmark = rs.Bookmark
Me.NameOfYourSubform.SetFocus.
DoCmd.GoToRecord , , acNewRec
End Sub

--
Steve Schapel, Microsoft Access MVP

Lin said:
Where exactly at the end of coding. Under what event Enter, Afterupdate ?
Lin
 
S

Steve Schapel

Lin,

There are a number of odd things about your code, which I am not
familiar with. However, there is an error which I have introduced, for
which I apologise. There should not be a . after the SetFocus. Does it
work ok if you remove that?

I am not sure of the purpose of the Str() function around the Combob4...
seems strange to me, but I left it before on the understanding that your
existing code was working correctly.

The other thing you may need to check is that AVInvoicedataentrysubf is
the name of the subform control on the main form, as against the name of
the form that is being used for the suform - these are not necessarily
the same.

But here's one thing I can't figure out. It appears that the main form
relates to POs, and the subform relates to Invoices. So why aren't you
showing the Vendor-related data on the main form, rather than the subform?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top