Form (new record) based on open form record

[K@foss-dental]

I have a form, frmCases, with a command button to open another form,
frmInvoice, based on the information on the current Case form info. I am
using the Dlookup function in the Default Value for the fields on the Invoice
form using the Case# as the common reference. All of the data appears
exactly as I want it to on the Invoice form EXCEPT...the Invoice Number. The
Invoice Number is an AutoNumber field. For whatever reason, the Invoice form
will not save the information displayed as a new record, nor will it advance
the Invoice Number to the next AutoNumber (it simply continues to display
'AutoNumber'.)
How can I save the default values displayed on the Invoice form as a new
record in the Invoice table, including the Invoice (auto) Number?

 

[strive4peace]

you can put a command button on the form to Save...

'~~~~~~~~~~~
Me.controlname = Me.controlname.DefaultValue
Me.Dirty = False
'~~~~~~~~~~~

all you have to do is assign ANY controlname ... then the
dirty property will set to true and you can save the record
in code


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[K@foss-dental]

Crystal,
Awesome to 'talk' with you again.
Not to be thick, but when I enter:

Private Sub Command79_Click()
Me.InvoiceNumber = Me.InvoiceNumber.DefaultValue
Me.Dirty = False
End Sub

I get an error message: "Runtime error: you can't assign a value to this
object."
Where am I going wrong? Do I need to assign a control name to the entire
form? To each field control name?
Thanks again.
~Kenn

 

[strive4peace]

Hi Kenn,

Thanks

Perhaps you cannot add records --

check the underlying recordset for your form

go to the design view of the form

open the properties window

select your form

click the Data tab in properties

click on the recordset property

click the builder button (...) to the right

you will see a screen that looks like a query design screen

switch to Datasheet view

if you can't add records here, you can't add records to the
form either

I am going out of town till Sat or Sun -- if you have more
questions, maybe someone else will pick this up... or I will
catch ya when I get back!

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[K@foss-dental]

Crystal,
I am able to add records in datasheet view. I am still not sure why the
auto number will not advance and allow a new record in the form view. I have
changed the properties (Edit, add, delete, data entry) to a variety of
combinations. Either I get the entire recordset (starting with the first
record, which I must then use the '*' to create a new record, which does not
have any of the information from the previously opened form that I am trying
to transfer information from) or I get the right information in the fields,
but it will not save the new record to the record set.
Hope you had a good weekend.
~Kenn

 

[strive4peace]

Hi Kenn,

Perhaps you are approaching this from the wrong direction...
since data is STORED in tables and you are trying to create
a new record, what about using an APPEND query to put the
record into the table...

'~~~~~~~~~~~~~~~~~
dim strSQL as string

strSQL = "INSERT INTO Tablename " _
& " (TextField, NumField, DateField ) " _
& " SELECT '" & strValue & "', " _
& numValue & ", " _
& "#" & datValue & "#" _
& ";"

currentdb.execute strSQL, dbFailOnError
currentdb.tabledefs.refresh

'~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[K@foss-dental]

Crystal,
Problem with your suggestion: I want to save the Invoice info in a separate
table (tblInvoices.) However, I have reduced the information being stored to
the CaseNumber and InvoiceNumber. This will be tied to a subform for the
Invoice Details.
Now here's the rub; I got the form to work as I wanted, advancing the auto
number and storing the new entry, so I know now that it can be done. Then I
changed a few other things on the form, LOST the ability to advance the
autonumber and store the record, and now I can't duplicate what I had
before!!! Talk about frustrating.

 

[strive4peace]

Hi Kenn,

how is this a problem?

if your form not based on tables? if you add a record to a
table then open your form, it will be there...

or, if your form is already open, you can refresh it to see
records created by others (uncluding SQL statements)

"LOST the ability to advance the autonumber and store the
record"

sounds to me like you have created a form that is based on a
recordset that is not updateable ... it is always a good
practice to base each form/subform on just one table. If
you do this, a lot of your frsutrations will go away.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*