Hello,

I have a problem, the user reported to me that he can not enter the invoice
number, because it is duplicate, our inv. number is primary key.

However if we checked it, we can not see the record in the database even
when we let computer find it, there is no that invoice number, however if we
input with that number, the system rejexct.

Can anyone help me how to find out that record?, is that being hidden by
the system?

Thanks in advance


--
H. Frank Situmorang

On Mon, 7 Jan 2008 21:02:00 -0800, Frank Situmorang <(E-Mail Removed)>
wrote:

>Hello,
>
>I have a problem, the user reported to me that he can not enter the invoice
>number, because it is duplicate, our inv. number is primary key.
>
>However if we checked it, we can not see the record in the database even
>when we let computer find it, there is no that invoice number, however if we
>input with that number, the system rejexct.
>
>Can anyone help me how to find out that record?, is that being hidden by
>the system?
>
>Thanks in advance

It's possible that you have a corrupt index: there might have been a record
with that value, it was deleted, but was left set in the index.

Get everyone out of your database first. Then MAKE A BACKUP of your database -
the backend if it's split. Open the backend and choose Tools... Database
Utilities... Compact and Repair. If that doesn't help, open the table in
design view; remove the primary key attribute of the field; compact the
database; and restore this field to being the primary key.

John W. Vinson [MVP]

That's not the reason in fact John. The reason is as follows:

I am the one who designed the form, and the form is based on a query which
consists of 3 table:

1. Supplier Invoices
2.Supplier
3.Project

The user did not complete the form with the job number and when he saved it,
and opened it again he could not see the record because the link is inner
link. When I opened the table in fact there is the record which was not shown
because there must be 3 table involved in the query while there were only 2

My question is how can we make it that job field in the form is a
requirement( compulsory) meaning that user can not save it unles they filled
the job number and supplier number, or we just make the table realtionship is
one to many but with outer joins

Thanks for your idea

--
H. Frank Situmorang


"John W. Vinson" wrote:

> On Mon, 7 Jan 2008 21:02:00 -0800, Frank Situmorang <(E-Mail Removed)>
> wrote:
>
> >Hello,
> >
> >I have a problem, the user reported to me that he can not enter the invoice
> >number, because it is duplicate, our inv. number is primary key.
> >
> >However if we checked it, we can not see the record in the database even
> >when we let computer find it, there is no that invoice number, however if we
> >input with that number, the system rejexct.
> >
> >Can anyone help me how to find out that record?, is that being hidden by
> >the system?
> >
> >Thanks in advance
>
> It's possible that you have a corrupt index: there might have been a record
> with that value, it was deleted, but was left set in the index.
>
> Get everyone out of your database first. Then MAKE A BACKUP of your database -
> the backend if it's split. Open the backend and choose Tools... Database
> Utilities... Compact and Repair. If that doesn't help, open the table in
> design view; remove the primary key attribute of the field; compact the
> database; and restore this field to being the primary key.
>
> John W. Vinson [MVP]
>

On Tue, 8 Jan 2008 01:13:01 -0800, Frank Situmorang <(E-Mail Removed)>
wrote:

>That's not the reason in fact John. The reason is as follows:
>
>I am the one who designed the form, and the form is based on a query which
>consists of 3 table:
>
>1. Supplier Invoices
>2.Supplier
>3.Project

Well... don't DO that then.

Instead, use a Combo Box for the supplier, and a Combo Box for the project.
Base the Form on the Supplier Invoices table directly, not on a query. The
combo can display the supplier name while storing the unique supplier ID; if
you want to see other information about the supplier (address, for example) on
screen, include those fields in the combo's RowSource query and put textboxes
on the form with control sources like

=comboboxname.Column(n)

where n is the zero based index of the field you want to see.

>The user did not complete the form with the job number and when he saved it,
>and opened it again he could not see the record because the link is inner
>link. When I opened the table in fact there is the record which was not shown
>because there must be 3 table involved in the query while there were only 2

You could change the INNER JOIN terms in the query to LEFT JOIN... but the
combo idea may well be preferable.

>My question is how can we make it that job field in the form is a
>requirement( compulsory) meaning that user can not save it unles they filled
>the job number and supplier number, or we just make the table realtionship is
>one to many but with outer joins

To make any field required, you can either make it a required field in the
table design, or use VBA code in the form's BeforeUpdate event to check.
Making the field required is good insurance but the error message may be
confusing to users. Form BeforeUpdate code might look like

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns As Integer
If Me!Supplier & "" = "" Then ' check to see if supplier is blank
iAns = MsgBox("Supplier must be specified; click OK to enter supplier," _
& " Cancel to erase form and start over:", vbOKCancel)
Cancel = True ' cancel the update
If iAns = vbCancel Then
Me.Undo ' erase the form if the user selected Cancel
End If
End If

<similar code for Project>

John W. Vinson [MVP]