Display values on input form before save the record

[Paulo Norberto]

Hello,

I am using Access 2007 ADP and SQL 2005

I have a table named tblA. This table has the fields A1, A2, A3. Field A1
has a “Data Type†of “intâ€, Identity increment of “1â€, is indexed (no
duplicates). Field A2 and A3 have a “Data Type†of “char†but they have no
problem.

I have a table named tblB. This table has the fields B1, B2, B3 and B4.
Field B1 has a “Data Type†of “intâ€, “Identity increment†of “1â€, is indexed
(no duplicates). Field B2 has a “Data Type†of “intâ€, do not have “Identity
incrementâ€, is indexed (duplicates OK).Field B3 has a “Data Type†of “charâ€
and B4 has a “Data Type†of “datetime†but they have no problem.

I have the query qryA as follow:
SELECT dbo. tblB.B1, dbo. tblB.B2, dbo. tblA.A2, dbo._ tblA.A3, dbo.
tblB.B3, dbo. tblB.B4
FROM dbo. tblA INNER JOIN
dbo. tblB ON dbo. tblA.A1 = dbo. tblB.FacturaCliID

I have a form to input data on table tblB with:
Record Source: qryA
Record Source Qualifier: dbo
Recordset Type: Updatable Snapshot
Unique Table: tblB

On this form the control B2 is a “Combo Box†with:
Control Source: B2
Row Source :
SELECT A1, A2
FROM tblA
ORDER BY A2
Row Source Type: Table/View/StoredProc
Bound column: 1
Column Count: 2
Column Widths: 0 cm; 5 cm

When I select a value in the “Combo Box†B2 I want that the bound “Text Boxâ€
A2 and A3 displays the corresponding values of the record selected in the
“Combo Box†B2 before the record has saved and the bound “Text Box†B1 gets
its auto number.
Until now I only get this if I save the record. There is any way that I can
display those two fields in a form before I save the record?
The reason that I need it is because I have five users doing the same work
on separated places and sometimes one of them needs to cancel the record
after see the results displayed in the “Text Box†A2 and A3. If the record
was preview saved it has to be deleted and the auto number will be lost. This
auto number Field B1 is used as Invoice number and we must not have jumps in
the sequence.


Thank you,

Paulo Norberto

 

[Sylvain Lafontaine]

You shouldn't use an identity field as the InvoiceNumber because there will
always be the possiblity of getting holes or jumps in the sequence; for
example if the communication or the sql-server went done or if there is a
glitch on the network.

Instead, you should use the identity fields for creating and associate the
records but you add another field for the InvoiceNumber itself. When the
order is confirmed, you'll have a procedure that will create and assign it
its new InvoiceNumber. This procedure of creating an InvoiceNumber will be
put inside a transaction with a lock in order to block anyone else until
it's finished so that if there is ever a glitch; the transaction will be
rolled back and the creation of the new InvoiceNumber fully cancelled; ie.,
without any effect on the assignation of the next number in the sequence.

This is the only way to be 100% to never any hole or jump in your sequence;
a legal requirement.

You can also create the InvoiceNumber when the record is created (but still
without using an identity field for this field) but with a flag that will
tell if the order is confirmed or not. This should satisfy the legal
requirement because a physical record will remain in the table.

Don't forget: never use an identity field for a sequence that must be
without hole or jump; however, this doesn't forbid you to use them as
primary keys; all you have to do is to add a second field for containing the
InvoiceNumber. The creation of the InvoiceNumber should be made inside a
transaction to prevent against any hole.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Paulo Norberto]

Hello Sylvain,

Thank you for your advice. I will correct it to avoid using the identity
field (it was the easy way to explain my problem).
Nevertheless, even with the procedure to create the invoice number I still
have the initial problem! How can I display the results of the fields
associated to the value selected in the combo box without saving the record?

Like:
One client enters in the store and asks for a product. The salesmen start a
new invoice and one of the fields that he has to fill is a combo box. The two
other fields are related to the combo box. If I don’t save the record I can’t
see values in those two fields. If I save the record I will see values in
those two fields.
When the salesmen show the result to the client, the client decides that he
doesn’t want the products. Now the salesmen will have to cancel the invoice
(delete the record).
If there is any way to show those values without saving the record it will
not be needed to delete it.
This is a fictitious situation but in my real process almost 50% of the
contracts (I do loan contracts not invoices) are canceled when clients see
the values that have to pay.

Thank you.

Regards,

Paulo

 

[Sylvain Lafontaine]

Like you have said, if you haven't saved it, it doesn't exist in the
database and if it doesn't exist in the database, you cannot show that on a
bound form. Probably that you can do that with an unbound form but this
will require a lot of reworking of your form.

A possibility would be to rework your form so that the record source of the
two comboboxes will be changed and show the right value when the underlying
bound value is Null but the combobox control of Access as a lot of trouble
when you try to bound them to a null value. You will have to make some
tests in order to see if you can achieve the desired effect.

An easier solution would be to use unbound comboxes or reuse the same
comboboxes by unbounding them in order to reflect the status of the
underlying form.

In all cases, you'll have to add some cooking to your form/comboboxes.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Paulo Norberto]

Hello Sylvain,

I fear that you will tell me this!
So, I create a new field (ContratoPenhorNum) in the table and with an update
query I fill them with the same value of the identity field. This new field
is an int, indexed (duplicates OK) because I need to allow null values.
In the form I rename the Identity filed to “Orçamento†(is a Quotation) and
the new field is called “Contrato nº†(is a Contract).
In the combo box I create an after update event with the following code:
DoCmd.RunCommand acCmdSaveRecord. Now I can see the values related with the
combo box.
In the form I change the save button to include the following code:
Private Sub SaveNum_Click()

Me!ContratoPenhorNum = DMax("ContratoPenhorNum", "ContratoPenhor") + 1
Me!Preview.Enabled = True (Preview is a button to preview the contract
to print and is set to false on form load)
Me!Preview.SetFocus
Me!SaveNum.Enabled = False

End Sub
In this way all records with the new (ContratoPenhorNum) field IsNull will
be treated as quotations and the contracts are the ones that
ContratoPenhorNum IsNotNull.
Thank you very much for your assistance.

Best Regards,

Paulo

 

[Sylvain Lafontaine]

Good but not perfect: the line « Me!ContratoPenhorNum =
DMax("ContratoPenhorNum", "ContratoPenhor") + 1
» does not garanty that you won't have any duplicate in a high concurrency
environment because of the time delay between the retrieval of the DMax()
value and the time the record is effectively saved to the database after
travelling back.

The only to ensure a perfect sequence with no duplicate is to use embed this
in a transaction where everyone else will be locked out until it's finished
and with ADP, the easiest way of coding this transaction would be with a
stored procedure on the SQL-Server or, alternatively, you could also start a
transaction with the proper isolation level by using the functionality
provided by ADO.

However, in your case, as you seem to be in a low concurrency environment,
you're probably safe to go this way but if you are doing this, if I were
you, I would take the precaution of adding a SQL-Server Constraint on the
table in order to make sure that no duplicate other than nulls are never
entered into the table for this field. This way, an error message will be
returned by SQL-Server if this ever happens. Your record won't be saved but
all your user will have to do will be to try saving it a second time but you
must provide a mecanism to detect and warn the user about this. You can
also try to simply automatically save the record a second time but you must
take into account the possibility that the previous saving has failed
because of a network error or the sql-server has go down.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Paulo Norberto]

Hello,

Once again thank you for your advice. I’m expecting it because you mention
it on your first reply. I try to do as you advice but I found that I don’t
have the necessary knowledge to do it! I’m sad that I can’t do it “perfectâ€
as you say but for now is as much as I can do! As soon as I have time to
study a little more I will try to correct it, implementing your
recommendations.
Thank you very much.

Best Regards,

Paulo

 

[Sylvain Lafontaine]

One easy way to ensure that there is no duplicate would be to set the value
of ContratoPenhorNum to a negative value (most likely the value of the
identity field * -1) instead of a null value and consider all negative
values to be quotation. This way, you can change the index to disallow for
duplicates.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[abraham Parra]

Good but not perfect: the line « Me!ContratoPenhorNum =
DMax("ContratoPenhorNum", "ContratoPenhor") + 1
» does not garanty that you won't have any duplicate in a high concurrency
environment because of the time delay between the retrieval of the DMax()
value and the time the record is effectively saved to the database after
travelling back.

The only to ensure a perfect sequence with no duplicate is to use embed this
in a transaction where everyone else will be locked out until it's finished
and with ADP, the easiest way of coding this transaction would be with a
stored procedure on the SQL-Server or, alternatively, you could also start a
transaction with the proper isolation level by using the functionality
provided by ADO.

However, in your case, as you seem to be in a low concurrency environment,
you're probably safe to go this way but if you are doing this, if I were
you, I would take the precaution of adding a SQL-Server Constraint on the
table in order to make sure that no duplicate other than nulls are never
entered into the table for this field. This way, an error message will be
returned by SQL-Server if this ever happens. Your record won't be saved but
all your user will have to do will be to try saving it a second time but you
must provide a mecanism to detect and warn the user about this. You can
also try to simply automatically save the record a second time but you must
take into account the possibility that the previous saving has failed
because of a network error or the sql-server has go down.

 

[Aleks]

Hello,

I am using Access 2007 ADP and SQL 2005

I have a table named tblA. This table has the fields A1, A2, A3. Field A1
has a “Data Type†of “intâ€, Identity increment of “1â€, is indexed (no
duplicates). Field A2 and A3 have a “Data Type†of “char†but they have no
problem.

I have a table named tblB. This table has the fields B1, B2, B3 and B4.
Field B1 has a “Data Type†of “intâ€, “Identity increment†of “1â€, is
indexed
(no duplicates). Field B2 has a “Data Type†of “intâ€, do not have
“Identity
incrementâ€, is indexed (duplicates OK).Field B3 has a “Data Type†of
“charâ€
and B4 has a “Data Type†of “datetime†but they have no problem.

I have the query qryA as follow:
SELECT dbo. tblB.B1, dbo. tblB.B2, dbo. tblA.A2, dbo._ tblA.A3, dbo.
tblB.B3, dbo. tblB.B4
FROM dbo. tblA INNER JOIN
dbo. tblB ON dbo. tblA.A1 = dbo. tblB.FacturaCliID

I have a form to input data on table tblB with:
Record Source: qryA
Record Source Qualifier: dbo
Recordset Type: Updatable Snapshot
Unique Table: tblB

On this form the control B2 is a “Combo Box†with:
Control Source: B2
Row Source :
SELECT A1, A2
FROM tblA
ORDER BY A2
Row Source Type: Table/View/StoredProc
Bound column: 1
Column Count: 2
Column Widths: 0 cm; 5 cm

When I select a value in the “Combo Box†B2 I want that the bound “Text
Boxâ€
A2 and A3 displays the corresponding values of the record selected in the
“Combo Box†B2 before the record has saved and the bound “Text Box†B1
gets
its auto number.
Until now I only get this if I save the record. There is any way that I
can
display those two fields in a form before I save the record?
The reason that I need it is because I have five users doing the same work
on separated places and sometimes one of them needs to cancel the record
after see the results displayed in the “Text Box†A2 and A3. If the record
was preview saved it has to be deleted and the auto number will be lost.
This
auto number Field B1 is used as Invoice number and we must not have jumps
in
the sequence.


Thank you,

Paulo Norberto