Record Duplicating Itself

[Melissa]

Hi there

Everytime I add a new client in my "Clients" form. It adds that client
twice. I can see the duplicates in my Combo box on another form, the each
client is listed twice? I have the "indexed / yes (no duplicates) set in my
Clients table.

What could be doing this?

Thank you kindly

Melissa

 

[Jeff Boyce]

Melissa

We aren't there. We don't know how your "Clients" form is processing what
you give it. We don't know how you are "adding a new client" to that form.
More specific description may lead to more specific suggestions.

For instance, is there any code running 'behind' that form? How are you
adding...?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Melissa]

Jeff,

My apologies for not providing enough information....

On my switchboard, I have a cmdButton "Add Job", which then opens my "Jobs"
form in add mode.
Then on my "jobs" form, I have a cmdButton which opens my "Clients" form in
add mode. I then type in my new clients details in the "clients" form, and
then close the "clients" form, and return to the "Jobs" form

I then click on the cmdButton "Refresh" on my "Jobs" form, and then I find
the client i just created in my Clients' combo box.

After selecting the client from the combo box on the "jobs" form, i continue
to fill in the other fields on the "jobs" form. Then after that, I have a
cmdButton - "Invoice Now", which opens the report "Invoice". When i open
the invoice, I have the same "JobNo" showing twice, each with the same
clients information.

This is how i realised that the clients are being added twice
I also tried to add a new client directly from the "clients" form by opening
it throught the objects window, and the same happens. Each client is
duplicated in the "Clients' table.

I hope I have provided the correct information

Thank you kindly

Melissa

 

[Jeff Boyce]

Melissa

Thanks for the additional information.

Now I need to ask for more!<g>

Someone can put ANY code behind a button click, so telling us about the
buttons on the forms won't really help diagnose why the button-clicks are
doing something unexpected.

Please provide the code itself, in addition to the user interface you've
already described.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Melissa]

Jeff

No problem at all...

Ok, for the cmdButton "Add Job" on the switchboard:

Private sub Command14_Click()
On Error GoTo Err_Command14_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Jobs"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_click

End Sub
_________

Then once "Jobs" form opens, - Click on cmdButton "AddClientDetails" Macro
is as follows:
OpenForm -
FormName - Clients
FilterName - leftblank
WhereCondition - leftblank
DataMode - Add
WindowMode - Dialog
____________________
Then i close the "clients" form using the X
THen upon returning to form "Jobs", refresh button macro as follows:

Requery
ControlName - VehicleRegNo

Requery
ControlName - ClientId
_____________________________________

Then after filling in other fields...click on cmdButton "InvoiceNow"
code:

Private Sub Command448_Click()
On Error GoTo Err_Command448_Click

Dim stDocName As String

stDocName = "JobsQuery"
DoCmd.OpenReport stDocName, acPreview

Me.HasPrinted = True

Exit_Command448_Click:
Exit Sub

Err_Command448_Click:
MsgBox Err.Description
Resume Exit_Command448_Click

End Sub
________________________________

Hope I did this right (not so clued up with code)

THank you very much

Melissa

 

[Jeff Boyce]

Melissa

So that last step opens a report. What data set is the report based on? If
that data for the report comes from a query, post the SQL statement of that
query.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Melissa]

Jeff

It does come from a query. But by looking at all tables etc many times, the
records do not seem to be duplicating themselves in the "Clients" table. I
saw that the duplicates were occurring in the "Client" combo box on the
"Jobs" Screen.

When I add a new job, the "Job" screen is displayed where I select a
"Company" from a list (this is the name of the Car dealership where the
windscreen chip is being repaired). I then select the "Client" name from a
list - this drop down is where is used to see each client appearing three
times. I deleted the combo box for "Client" and re-created it. Now, it is
no longer duplicating client names.

I imagine that is why the report was showing each client that many times as
the report (invoice) was getting the "Client" information from that combo
box. That seems to be ok now.

The problem im having at the moment is that I cannot get the "Company's "
address etc to show on the invoice. (The Car dealerships address)

I have gone into the record source of the report and tried to add "Address"
information - but it will not let me even "Show" the "Company" table, it
says - Type Mismatch in Expression

Therefore i cannot add the company's address / contact details to the
report.

I would have used eg: Forms![Jobs]![Address] , but the "Jobs" form does
not display any of those details for the Company?

I hope this all makes sense.......

Thank you kindly

Melissa

 

[Jeff Boyce]

Melissa

The "type mismatch" error message is an indication that you are trying to
join two tables on fields that are dissimilar. For example, trying to
connect a numeric field with a text field.

One way this could happen is if your table(s) contains lookup datatype
field(s). These store one thing (the lookup table's primary key) but
display something else (the looked-up value). Thus, when you try to build a
query, connection on the value you believe is stored in the field, you could
get a "type mismatch" error.

This is just one way to get that error. Rule out the "lookup" field
datatype first...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff

It does come from a query. But by looking at all tables etc many times, the
records do not seem to be duplicating themselves in the "Clients" table. I
saw that the duplicates were occurring in the "Client" combo box on the
"Jobs" Screen.

When I add a new job, the "Job" screen is displayed where I select a
"Company" from a list (this is the name of the Car dealership where the
windscreen chip is being repaired). I then select the "Client" name from a
list - this drop down is where is used to see each client appearing three
times. I deleted the combo box for "Client" and re-created it. Now, it is
no longer duplicating client names.

I imagine that is why the report was showing each client that many times as
the report (invoice) was getting the "Client" information from that combo
box. That seems to be ok now.

The problem im having at the moment is that I cannot get the "Company's "
address etc to show on the invoice. (The Car dealerships address)

I have gone into the record source of the report and tried to add "Address"
information - but it will not let me even "Show" the "Company" table, it
says - Type Mismatch in Expression

Therefore i cannot add the company's address / contact details to the
report.

I would have used eg: Forms![Jobs]![Address] , but the "Jobs" form does
not display any of those details for the Company?

I hope this all makes sense.......

Thank you kindly

Melissa

Melissa

So that last step opens a report. What data set is the report based on?
If
that data for the report comes from a query, post the SQL statement of
that
query.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

duplicates)
set

 

[Melissa]

Hi Jeff

Thank you very much for your reply. This seems to have solved the problem -
I have got it right to show the address information on the invoice.

Now I have another problem
I need the invoice to display an "invoiceNo", which needs to be a recurring
number (auto number), however if I add "InvoiceNo" to my Jobs Table and
"Jobs" form, it will not allow me to select it as "autonumber", as
"autonumber" can only be used once in a table.

How could I put an autonumber on the invoice? also, i would like to put a
combo box on my "jobs" form in which i can select the "invoiceNo" for that
specific job from a list, and by selecting it in the combo box, the invoice
will be displayed?

thank you kindly

Melissa

Melissa

The "type mismatch" error message is an indication that you are trying to
join two tables on fields that are dissimilar. For example, trying to
connect a numeric field with a text field.

One way this could happen is if your table(s) contains lookup datatype
field(s). These store one thing (the lookup table's primary key) but
display something else (the looked-up value). Thus, when you try to build
a
query, connection on the value you believe is stored in the field, you
could
get a "type mismatch" error.

This is just one way to get that error. Rule out the "lookup" field
datatype first...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff

It does come from a query. But by looking at all tables etc many times, the
records do not seem to be duplicating themselves in the "Clients" table. I
saw that the duplicates were occurring in the "Client" combo box on the
"Jobs" Screen.

When I add a new job, the "Job" screen is displayed where I select a
"Company" from a list (this is the name of the Car dealership where the
windscreen chip is being repaired). I then select the "Client" name from a
list - this drop down is where is used to see each client appearing three
times. I deleted the combo box for "Client" and re-created it. Now, it is
no longer duplicating client names.

I imagine that is why the report was showing each client that many times as
the report (invoice) was getting the "Client" information from that combo
box. That seems to be ok now.

The problem im having at the moment is that I cannot get the "Company's "
address etc to show on the invoice. (The Car dealerships address)

I have gone into the record source of the report and tried to add "Address"
information - but it will not let me even "Show" the "Company" table, it
says - Type Mismatch in Expression

Therefore i cannot add the company's address / contact details to the
report.

I would have used eg: Forms![Jobs]![Address] , but the "Jobs" form does
not display any of those details for the Company?

I hope this all makes sense.......

Thank you kindly

Melissa


message

Melissa

So that last step opens a report. What data set is the report based
on?
If
that data for the report comes from a query, post the SQL statement of
that
query.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff

No problem at all...

Ok, for the cmdButton "Add Job" on the switchboard:

Private sub Command14_Click()
On Error GoTo Err_Command14_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Jobs"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_click

End Sub
_________

Then once "Jobs" form opens, - Click on cmdButton "AddClientDetails"
Macro
is as follows:
OpenForm -
FormName - Clients
FilterName - leftblank
WhereCondition - leftblank
DataMode - Add
WindowMode - Dialog
____________________
Then i close the "clients" form using the X
THen upon returning to form "Jobs", refresh button macro as follows:

Requery
ControlName - VehicleRegNo

Requery
ControlName - ClientId
_____________________________________

Then after filling in other fields...click on cmdButton "InvoiceNow"
code:

Private Sub Command448_Click()
On Error GoTo Err_Command448_Click

Dim stDocName As String

stDocName = "JobsQuery"
DoCmd.OpenReport stDocName, acPreview

Me.HasPrinted = True

Exit_Command448_Click:
Exit Sub

Err_Command448_Click:
MsgBox Err.Description
Resume Exit_Command448_Click

End Sub
________________________________

Hope I did this right (not so clued up with code)

THank you very much

Melissa



message
Melissa

Thanks for the additional information.

Now I need to ask for more!<g>

Someone can put ANY code behind a button click, so telling us about the
buttons on the forms won't really help diagnose why the
button-clicks
are
doing something unexpected.

Please provide the code itself, in addition to the user interface
you've
already described.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff,

My apologies for not providing enough information....

On my switchboard, I have a cmdButton "Add Job", which then opens
my
"Jobs"
form in add mode.
Then on my "jobs" form, I have a cmdButton which opens my "Clients"
form
in
add mode. I then type in my new clients details in the "clients"
form,
and
then close the "clients" form, and return to the "Jobs" form

I then click on the cmdButton "Refresh" on my "Jobs" form, and then I
find
the client i just created in my Clients' combo box.

After selecting the client from the combo box on the "jobs" form, i
continue
to fill in the other fields on the "jobs" form. Then after that, I
have
a
cmdButton - "Invoice Now", which opens the report "Invoice".
When i
open
the invoice, I have the same "JobNo" showing twice, each with the same
clients information.

This is how i realised that the clients are being added twice
I also tried to add a new client directly from the "clients" form
by
opening
it throught the objects window, and the same happens. Each client is
duplicated in the "Clients' table.

I hope I have provided the correct information

Thank you kindly

Melissa


message
Melissa

We aren't there. We don't know how your "Clients" form is
processing
what
you give it. We don't know how you are "adding a new client" to
that
form.
More specific description may lead to more specific suggestions.

For instance, is there any code running 'behind' that form? How are
you
adding...?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi there

Everytime I add a new client in my "Clients" form. It adds that
client
twice. I can see the duplicates in my Combo box on another
form,
the
each
client is listed twice? I have the "indexed / yes (no duplicates)
set
in
my
Clients table.

What could be doing this?

Thank you kindly

Melissa

 

[Jeff Boyce]

Melissa

In the future, you'll find that you get a lot more "eyes" on your issues if
you post "another problem" as a new post, rather than burying it deep inside
an existing thread.

An Access Autonumber is a long integer generated by Access for use as a
unique row identifier. They are generally unfit for human consumption.

If you want to add a number you can control, do a search (?at
mvps.org/access) for "Custom Autonumber", which will give you a way to
generate sequence numbers.

Why do you feel you need such a "number"? If you are using it to "count"
the number of invoices, don't bother! Use a query to count records.

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi Jeff

Thank you very much for your reply. This seems to have solved the problem -
I have got it right to show the address information on the invoice.

Now I have another problem
I need the invoice to display an "invoiceNo", which needs to be a recurring
number (auto number), however if I add "InvoiceNo" to my Jobs Table and
"Jobs" form, it will not allow me to select it as "autonumber", as
"autonumber" can only be used once in a table.

How could I put an autonumber on the invoice? also, i would like to put a
combo box on my "jobs" form in which i can select the "invoiceNo" for that
specific job from a list, and by selecting it in the combo box, the invoice
will be displayed?

thank you kindly

Melissa

Melissa

The "type mismatch" error message is an indication that you are trying to
join two tables on fields that are dissimilar. For example, trying to
connect a numeric field with a text field.

One way this could happen is if your table(s) contains lookup datatype
field(s). These store one thing (the lookup table's primary key) but
display something else (the looked-up value). Thus, when you try to build
a
query, connection on the value you believe is stored in the field, you
could
get a "type mismatch" error.

This is just one way to get that error. Rule out the "lookup" field
datatype first...

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff

It does come from a query. But by looking at all tables etc many

times,
the

records do not seem to be duplicating themselves in the "Clients"

table.
I

saw that the duplicates were occurring in the "Client" combo box on the
"Jobs" Screen.

When I add a new job, the "Job" screen is displayed where I select a
"Company" from a list (this is the name of the Car dealership where the
windscreen chip is being repaired). I then select the "Client" name

from
a

list - this drop down is where is used to see each client appearing three
times. I deleted the combo box for "Client" and re-created it. Now,

it
is

no longer duplicating client names.

I imagine that is why the report was showing each client that many

times
as

the report (invoice) was getting the "Client" information from that combo
box. That seems to be ok now.

The problem im having at the moment is that I cannot get the "Company's "
address etc to show on the invoice. (The Car dealerships address)

I have gone into the record source of the report and tried to add "Address"
information - but it will not let me even "Show" the "Company" table, it
says - Type Mismatch in Expression

Therefore i cannot add the company's address / contact details to the
report.

I would have used eg: Forms![Jobs]![Address] , but the "Jobs" form does
not display any of those details for the Company?

I hope this all makes sense.......

Thank you kindly

Melissa


message
Melissa

So that last step opens a report. What data set is the report based
on?
If
that data for the report comes from a query, post the SQL statement of
that
query.


--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff

No problem at all...

Ok, for the cmdButton "Add Job" on the switchboard:

Private sub Command14_Click()
On Error GoTo Err_Command14_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Jobs"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_click

End Sub
_________

Then once "Jobs" form opens, - Click on cmdButton "AddClientDetails"
Macro
is as follows:
OpenForm -
FormName - Clients
FilterName - leftblank
WhereCondition - leftblank
DataMode - Add
WindowMode - Dialog
____________________
Then i close the "clients" form using the X
THen upon returning to form "Jobs", refresh button macro as follows:

Requery
ControlName - VehicleRegNo

Requery
ControlName - ClientId
_____________________________________

Then after filling in other fields...click on cmdButton "InvoiceNow"
code:

Private Sub Command448_Click()
On Error GoTo Err_Command448_Click

Dim stDocName As String

stDocName = "JobsQuery"
DoCmd.OpenReport stDocName, acPreview

Me.HasPrinted = True

Exit_Command448_Click:
Exit Sub

Err_Command448_Click:
MsgBox Err.Description
Resume Exit_Command448_Click

End Sub
________________________________

Hope I did this right (not so clued up with code)

THank you very much

Melissa



message
Melissa

Thanks for the additional information.

Now I need to ask for more!<g>

Someone can put ANY code behind a button click, so telling us

about
the

buttons on the forms won't really help diagnose why the
button-clicks
are
doing something unexpected.

Please provide the code itself, in addition to the user interface
you've
already described.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Jeff,

My apologies for not providing enough information....

On my switchboard, I have a cmdButton "Add Job", which then opens
my
"Jobs"
form in add mode.
Then on my "jobs" form, I have a cmdButton which opens my "Clients"
form
in
add mode. I then type in my new clients details in the "clients"
form,
and
then close the "clients" form, and return to the "Jobs" form

I then click on the cmdButton "Refresh" on my "Jobs" form, and

then
I

find
the client i just created in my Clients' combo box.

After selecting the client from the combo box on the "jobs" form, i
continue
to fill in the other fields on the "jobs" form. Then after that, I
have
a
cmdButton - "Invoice Now", which opens the report "Invoice".
When i
open
the invoice, I have the same "JobNo" showing twice, each with the same
clients information.

This is how i realised that the clients are being added twice
I also tried to add a new client directly from the "clients" form
by
opening
it throught the objects window, and the same happens. Each

client
is

duplicated in the "Clients' table.

I hope I have provided the correct information

Thank you kindly

Melissa


message
Melissa

We aren't there. We don't know how your "Clients" form is
processing
what
you give it. We don't know how you are "adding a new client" to
that
form.
More specific description may lead to more specific suggestions.

For instance, is there any code running 'behind' that form?

How
are

you
adding...?

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Hi there

Everytime I add a new client in my "Clients" form. It adds that
client
twice. I can see the duplicates in my Combo box on another
form,
the
each
client is listed twice? I have the "indexed / yes (no duplicates)
set
in
my
Clients table.

What could be doing this?

Thank you kindly

Melissa