Sync Subform with Mainform

[Robert Jacobs]

Please advise...

I currently have a Mainform (Customers) and a Subform (Service
Requests) with a one to many relationship (one customer, many service
requests) with a CustomerID that is unique for each customer and is
autofilled on the subform. Subform contains field called TicketID
(which there could be multiple instances of and is not referenced on
the mainform at all). I want to be able to type in a TicketID and
have the mainform navigate to the correct customer. I have a cmbbox
with following setup:

SELECT DISTINCT [Service Records].[Ticket ID], [Service
Records].CustomerID FROM [Service Records];

Marshall gave the following advice to someone with a similar problem,
but I can't get it to work. After Me.Recordset, there is no option
for FindFirst, and I don't know what to put in the "[Serial]="
section. Any help is appreciated.

Ok, the combo box is unbound ans you want it to make the
main form display (navigate to) the record for the selected
computer. If that's a correct understanding of your
arrangement, then the code in the combo box's AfterUpdate
event needs to be something like this:

If Serial is a numeric type field:
Me.Recordset.FindFirst "[Serial]=" & Me.combobox
If serial is a Text field:
Me.Recordset.FindFirst "[Serial]=""" & Me.combobox & """"

[lesperancer]

On Apr 9, 10:08 am, "Robert Jacobs" <[e-mail]:

Please advise...

I currently have a Mainform (Customers) and a Subform (Service
Requests) with a one to many relationship (one customer, many service
requests) with a CustomerID that is unique for each customer and is
autofilled on the subform. Subform contains field called TicketID
(which there could be multiple instances of and is not referenced on
the mainform at all). I want to be able to type in a TicketID and
have the mainform navigate to the correct customer. I have a cmbbox
with following setup:

SELECT DISTINCT [Service Records].[Ticket ID], [Service
Records].CustomerID FROM [Service Records];

Marshall gave the following advice to someone with a similar problem,
but I can't get it to work. After Me.Recordset, there is no option
for FindFirst, and I don't know what to put in the "[Serial]="
section. Any help is appreciated.

Ok, the combo box is unbound ans you want it to make the
main form display (navigate to) the record for the selected
computer. If that's a correct understanding of your
arrangement, then the code in the combo box's AfterUpdate
event needs to be something like this:

If Serial is a numeric type field:
Me.Recordset.FindFirst "[Serial]=" & Me.combobox

If serial is a Text field:
Me.Recordset.FindFirst "[Serial]=""" & Me.combobox & """"

me.recordset.... refers to the current form
if your combobox is on the subform, the above refers to the subform

me.parent.form.recordset.... would refer to the main form's recordset

[Robert Jacobs]

On Apr 9, 11:34 am, [e-mail] wrote:

On Apr 9, 10:08 am, "Robert Jacobs" <[e-mail]:

Please advise...

I currently have a Mainform (Customers) and a Subform (Service
Requests) with a one to many relationship (one customer, many service
requests) with a CustomerID that is unique for each customer and is
autofilled on the subform. Subform contains field called TicketID
(which there could be multiple instances of and is not referenced on
the mainform at all). I want to be able to type in a TicketID and
have the mainform navigate to the correct customer. I have a cmbbox
with following setup:

SELECT DISTINCT [Service Records].[Ticket ID], [Service
Records].CustomerID FROM [Service Records];

Marshall gave the following advice to someone with a similar problem,
but I can't get it to work. After Me.Recordset, there is no option
for FindFirst, and I don't know what to put in the "[Serial]="
section. Any help is appreciated.

Ok, the combo box is unbound ans you want it to make the
main form display (navigate to) the record for the selected
computer. If that's a correct understanding of your
arrangement, then the code in the combo box's AfterUpdate
event needs to be something like this:

If Serial is a numeric type field:
Me.Recordset.FindFirst "[Serial]=" & Me.combobox

If serial is a Text field:
Me.Recordset.FindFirst "[Serial]=""" & Me.combobox & """"

me.recordset.... refers to the current form
if your combobox is on the subform, the above refers to the subform

me.parent.form.recordset.... would refer to the main form's recordset- Hide quoted text -

- Show quoted text -

Thanks. The combobox is on the mainform, and refers to the subform.
when I type me.recordset. I hear a sound like "dun" and then it
doesn't keep trying to autofill the FindFirst entry. If I do
me.parent. I get the same sound and it won't keep filling the word
form. Am I doing something wrong, or is it supposed to do this?
Also, what do I need to put in the "[serial]="" section from above?

[Marshall Barton]

Robert Jacobs wrote:

>I currently have a Mainform (Customers) and a Subform (Service
Requests) with a one to many relationship (one customer, many service
requests) with a CustomerID that is unique for each customer and is
autofilled on the subform. Subform contains field called TicketID
(which there could be multiple instances of and is not referenced on
the mainform at all). I want to be able to type in a TicketID and
have the mainform navigate to the correct customer. I have a cmbbox
with following setup:

SELECT DISTINCT [Service Records].[Ticket ID], [Service
Records].CustomerID FROM [Service Records];

Marshall gave the following advice to someone with a similar problem,
but I can't get it to work. After Me.Recordset, there is no option
for FindFirst, and I don't know what to put in the "[Serial]="
section. Any help is appreciated.

Ok, the combo box is unbound ans you want it to make the
main form display (navigate to) the record for the selected
computer. If that's a correct understanding of your
arrangement, then the code in the combo box's AfterUpdate
event needs to be something like this:

If Serial is a numeric type field:
Me.Recordset.FindFirst "[Serial]=" & Me.combobox
If serial is a Text field:
Me.Recordset.FindFirst "[Serial]=""" & Me.combobox & """"

I am having trouble following what you want to happen here.
I think you want the main form to navigate to the customer
selected in the combo box. Assuming the combo box's
BoundColumn is set to 1, I think you would use this kind of
statement:

Me.Recordset.FindFirst "CustomerID=" & combobox.Column(1)

But, if you get a method not found error, it's probably
because the form is using an ADO recordset instead of a DAO
recordset. ADO uses the Find method instead of FindFirst.

OTOH, your main form should not be loading all the customer
records, which would be a gross waste of resources. Instead
the form should open with no customer records and only load
one customer record after a selection is made in the combo
box.

--
Marsh

[Robert Jacobs]

On Apr 10, 2:45 pm, Marshall Barton <[e-mail]:

Robert Jacobs wrote:

I currently have a Mainform (Customers) and a Subform (Service
Requests) with a one to many relationship (one customer, many service
requests) with a CustomerID that is unique for each customer and is
autofilled on the subform. Subform contains field called TicketID
(which there could be multiple instances of and is not referenced on
the mainform at all). I want to be able to type in a TicketID and
have the mainform navigate to the correct customer. I have a cmbbox
with following setup:

SELECT DISTINCT [Service Records].[Ticket ID], [Service
Records].CustomerID FROM [Service Records];

Marshall gave the following advice to someone with a similar problem,
but I can't get it to work. After Me.Recordset, there is no option
for FindFirst, and I don't know what to put in the "[Serial]="
section. Any help is appreciated.

Ok, the combo box is unbound ans you want it to make the
main form display (navigate to) the record for the selected
computer. If that's a correct understanding of your
arrangement, then the code in the combo box's AfterUpdate
event needs to be something like this:

If Serial is a numeric type field:
Me.Recordset.FindFirst "[Serial]=" & Me.combobox

If serial is a Text field:
Me.Recordset.FindFirst "[Serial]=""" & Me.combobox & """"

I am having trouble following what you want to happen here.
I think you want the main form to navigate to the customer
selected in the combo box. Assuming the combo box's
BoundColumn is set to 1, I think you would use this kind of
statement:

Me.Recordset.FindFirst "CustomerID=" & combobox.Column(1)

But, if you get a method not found error, it's probably
because the form is using an ADO recordset instead of a DAO
recordset. ADO uses the Find method instead of FindFirst.

OTOH, your main form should not be loading all the customer
records, which would be a gross waste of resources. Instead
the form should open with no customer records and only load
one customer record after a selection is made in the combo
box.

--
Marsh- Hide quoted text -

- Show quoted text -

Ok. That's not what I was trying to accomplish. I will try to be a
little more clear (sorry). I have multiple customers (which I do want
loaded on the mainform (customers) when it opens, there are never more
than 50 customers). Each customer is automatically assigned a
customer ID (which he never sees-it is an autonumber that is
irrelivant to the user, just there because there has to be a primary
key). This customer ID is automatically propogated to the linked
subform (service requests) for every record for that customer. In the
subform there is a txtbox that holds a ticket ID (which is not
necessarily unique, and each customer has multiple ticket IDs). I
have also added an embedded subform (just there so he can see the
ticket IDs and is not necessary) that shows all of the ticket IDs for
the currently displayed customer. I have created a combobox that is
on the mainform that is autopopulated with all of the ticket IDs for
ALL customers. I would for him to be able to select a ticket ID (or
type it in) and have the mainform go to the next customer that
contains that ticket ID in one of it's records in the subform.

Is this possible?

[lesperancer]

On Apr 10, 2:07 pm, "Robert Jacobs" <[e-mail]:

On Apr 10, 2:45 pm, Marshall Barton <[e-mail]:

Robert Jacobs wrote:

>I currently have a Mainform (Customers) and a Subform (Service
>Requests) with a one to many relationship (one customer, many service
>requests) with a CustomerID that is unique for each customer and is
>autofilled on the subform. Subform contains field called TicketID
>(which there could be multiple instances of and is not referenced on
>the mainform at all). I want to be able to type in a TicketID and
>have the mainform navigate to the correct customer. I have a cmbbox
>with following setup:

>SELECT DISTINCT [Service Records].[Ticket ID], [Service
>Records].CustomerID FROM [Service Records];

>Marshall gave the following advice to someone with a similar problem,
>but I can't get it to work. After Me.Recordset, there is no option
>for FindFirst, and I don't know what to put in the "[Serial]="
>section. Any help is appreciated.

>Ok, the combo box is unbound ans you want it to make the
>main form display (navigate to) the record for the selected
>computer. If that's a correct understanding of your
>arrangement, then the code in the combo box's AfterUpdate
>event needs to be something like this:

>If Serial is a numeric type field:
Me.Recordset.FindFirst "[Serial]=" & Me.combobox

>If serial is a Text field:
Me.Recordset.FindFirst "[Serial]=""" & Me.combobox & """"

I am having trouble following what you want to happen here.
I think you want the main form to navigate to the customer
selected in the combo box. Assuming the combo box's
BoundColumn is set to 1, I think you would use this kind of
statement:

Me.Recordset.FindFirst "CustomerID=" & combobox.Column(1)

But, if you get a method not found error, it's probably
because the form is using an ADO recordset instead of a DAO
recordset. ADO uses the Find method instead of FindFirst.

OTOH, your main form should not be loading all the customer
records, which would be a gross waste of resources. Instead
the form should open with no customer records and only load
one customer record after a selection is made in the combo
box.

--
Marsh- Hide quoted text -

- Show quoted text -

Ok. That's not what I was trying to accomplish. I will try to be a
little more clear (sorry). I have multiple customers (which I do want
loaded on the mainform (customers) when it opens, there are never more
than 50 customers). Each customer is automatically assigned a
customer ID (which he never sees-it is an autonumber that is
irrelivant to the user, just there because there has to be a primary
key). This customer ID is automatically propogated to the linked
subform (service requests) for every record for that customer. In the
subform there is a txtbox that holds a ticket ID (which is not
necessarily unique, and each customer has multiple ticket IDs). I
have also added an embedded subform (just there so he can see the
ticket IDs and is not necessary) that shows all of the ticket IDs for
the currently displayed customer. I have created a combobox that is
on the mainform that is autopopulated with all of the ticket IDs for
ALL customers. I would for him to be able to select a ticket ID (or
type it in) and have the mainform go to the next customer that
contains that ticket ID in one of it's records in the subform.

Is this possible?- Hide quoted text -

- Show quoted text -

not sure want you mean by 'go to the next customer', but
you can do this

dim lngCustId as long

lngCust = dfirst("custId","service records", "ticketId = " &
me.combobox)
Me.Recordset.FindFirst "[custId]=" & lngCust

to give you the 'first' customer with a ticketId that matches what was
entered / selected

you can change the 'where' (ie. "ticketId = " & me.combobox) clause to
get a different customer id

[Robert Jacobs]

On Apr 10, 3:42 pm, [e-mail] wrote:

On Apr 10, 2:07 pm, "Robert Jacobs" <[e-mail]:

On Apr 10, 2:45 pm, Marshall Barton <[e-mail]:

Robert Jacobs wrote:
I currently have a Mainform (Customers) and a Subform (Service
Requests) with a one to many relationship (one customer, many service
requests) with a CustomerID that is unique for each customer and is
autofilled on the subform. Subform contains field called TicketID
(which there could be multiple instances of and is not referenced on
the mainform at all). I want to be able to type in a TicketID and
have the mainform navigate to the correct customer. I have a cmbbox
with following setup:

SELECT DISTINCT [Service Records].[Ticket ID], [Service
Records].CustomerID FROM [Service Records];

Marshall gave the following advice to someone with a similar problem,
but I can't get it to work. After Me.Recordset, there is no option
for FindFirst, and I don't know what to put in the "[Serial]="
section. Any help is appreciated.

Ok, the combo box is unbound ans you want it to make the
main form display (navigate to) the record for the selected
computer. If that's a correct understanding of your
arrangement, then the code in the combo box's AfterUpdate
event needs to be something like this:

If Serial is a numeric type field:
Me.Recordset.FindFirst "[Serial]=" & Me.combobox

If serial is a Text field:
Me.Recordset.FindFirst "[Serial]=""" & Me.combobox & """"

I am having trouble following what you want to happen here.
I think you want the main form to navigate to the customer
selected in the combo box. Assuming the combo box's
BoundColumn is set to 1, I think you would use this kind of
statement:

Me.Recordset.FindFirst "CustomerID=" & combobox.Column(1)

But, if you get a method not found error, it's probably
because the form is using an ADO recordset instead of a DAO
recordset. ADO uses the Find method instead of FindFirst.

OTOH, your main form should not be loading all the customer
records, which would be a gross waste of resources. Instead
the form should open with no customer records and only load
one customer record after a selection is made in the combo
box.

--
Marsh- Hide quoted text -

- Show quoted text -

Ok. That's not what I was trying to accomplish. I will try to be a
little more clear (sorry). I have multiple customers (which I do want
loaded on the mainform (customers) when it opens, there are never more
than 50 customers). Each customer is automatically assigned a
customer ID (which he never sees-it is an autonumber that is
irrelivant to the user, just there because there has to be a primary
key). This customer ID is automatically propogated to the linked
subform (service requests) for every record for that customer. In the
subform there is a txtbox that holds a ticket ID (which is not
necessarily unique, and each customer has multiple ticket IDs). I
have also added an embedded subform (just there so he can see the
ticket IDs and is not necessary) that shows all of the ticket IDs for
the currently displayed customer. I have created a combobox that is
on the mainform that is autopopulated with all of the ticket IDs for
ALL customers. I would for him to be able to select a ticket ID (or
type it in) and have the mainform go to the next customer that
contains that ticket ID in one of it's records in the subform.

Is this possible?- Hide quoted text -

- Show quoted text -

not sure want you mean by 'go to the next customer', but
you can do this

dim lngCustId as long

lngCust = dfirst("custId","service records", "ticketId = " &
me.combobox)
Me.Recordset.FindFirst "[custId]=" & lngCust

to give you the 'first' customer with a ticketId that matches what was
entered / selected

you can change the 'where' (ie. "ticketId = " & me.combobox) clause to
get a different customer id- Hide quoted text -

- Show quoted text -

What I mean by go to the next customer is I want the mainform to
navigate to the customer that contains the entered ticketID. I don't
want the user type in a ticket ID and be given a customer ID, I want
the user to type a ticket ID and have the mainform navigate to the
appropriate customer (or customer ID)

I will try what you have typed above.

[Marshall Barton]

Robert Jacobs wrote:

>On Apr 10, 2:45 pm, Marshall Barton <[e-mail]:

>Robert Jacobs wrote:

>I currently have a Mainform (Customers) and a Subform (Service
Requests) with a one to many relationship (one customer, many service
requests) with a CustomerID that is unique for each customer and is
autofilled on the subform. Subform contains field called TicketID
(which there could be multiple instances of and is not referenced on
the mainform at all). I want to be able to type in a TicketID and
have the mainform navigate to the correct customer. I have a cmbbox
with following setup:

>SELECT DISTINCT [Service Records].[Ticket ID], [Service
Records].CustomerID FROM [Service Records];

>Marshall gave the following advice to someone with a similar problem,
but I can't get it to work. After Me.Recordset, there is no option
for FindFirst, and I don't know what to put in the "[Serial]="
section. Any help is appreciated.

>Ok, the combo box is unbound ans you want it to make the
main form display (navigate to) the record for the selected
computer. If that's a correct understanding of your
arrangement, then the code in the combo box's AfterUpdate
event needs to be something like this:

>If Serial is a numeric type field:
Me.Recordset.FindFirst "[Serial]=" & Me.combobox

>If serial is a Text field:
Me.Recordset.FindFirst "[Serial]=""" & Me.combobox & """"

I am having trouble following what you want to happen here.
I think you want the main form to navigate to the customer
selected in the combo box. Assuming the combo box's
BoundColumn is set to 1, I think you would use this kind of
statement:

Me.Recordset.FindFirst "CustomerID=" & combobox.Column(1)

But, if you get a method not found error, it's probably
because the form is using an ADO recordset instead of a DAO
recordset. ADO uses the Find method instead of FindFirst.

OTOH, your main form should not be loading all the customer
records, which would be a gross waste of resources. Instead
the form should open with no customer records and only load
one customer record after a selection is made in the combo
box.

Ok. That's not what I was trying to accomplish. I will try to be a
little more clear (sorry). I have multiple customers (which I do want
loaded on the mainform (customers) when it opens, there are never more
than 50 customers). Each customer is automatically assigned a
customer ID (which he never sees-it is an autonumber that is
irrelivant to the user, just there because there has to be a primary
key). This customer ID is automatically propogated to the linked
subform (service requests) for every record for that customer. In the
subform there is a txtbox that holds a ticket ID (which is not
necessarily unique, and each customer has multiple ticket IDs). I
have also added an embedded subform (just there so he can see the
ticket IDs and is not necessary) that shows all of the ticket IDs for
the currently displayed customer. I have created a combobox that is
on the mainform that is autopopulated with all of the ticket IDs for
ALL customers. I would for him to be able to select a ticket ID (or
type it in) and have the mainform go to the next customer that
contains that ticket ID in one of it's records in the subform.

Now I am even more confused, because that's what I thought
you wanted and what I intended to happen when a user
selected a ticket in the combo box.

Can you explain what you did, what effect it produced and
what happened?

--
Marsh

[Robert Jacobs]

On Apr 10, 3:59 pm, Marshall Barton <[e-mail]:

Robert Jacobs wrote:

On Apr 10, 2:45 pm, Marshall Barton <[e-mail]:

Robert Jacobs wrote:
I currently have a Mainform (Customers) and a Subform (Service
Requests) with a one to many relationship (one customer, many service
requests) with a CustomerID that is unique for each customer and is
autofilled on the subform. Subform contains field called TicketID
(which there could be multiple instances of and is not referenced on
the mainform at all). I want to be able to type in a TicketID and
have the mainform navigate to the correct customer. I have a cmbbox
with following setup:

SELECT DISTINCT [Service Records].[Ticket ID], [Service
Records].CustomerID FROM [Service Records];

Marshall gave the following advice to someone with a similar problem,
but I can't get it to work. After Me.Recordset, there is no option
for FindFirst, and I don't know what to put in the "[Serial]="
section. Any help is appreciated.

Ok, the combo box is unbound ans you want it to make the
main form display (navigate to) the record for the selected
computer. If that's a correct understanding of your
arrangement, then the code in the combo box's AfterUpdate
event needs to be something like this:

If Serial is a numeric type field:
Me.Recordset.FindFirst "[Serial]=" & Me.combobox

If serial is a Text field:
Me.Recordset.FindFirst "[Serial]=""" & Me.combobox & """"

I am having trouble following what you want to happen here.
I think you want the main form to navigate to the customer
selected in the combo box. Assuming the combo box's
BoundColumn is set to 1, I think you would use this kind of
statement:

Me.Recordset.FindFirst "CustomerID=" & combobox.Column(1)

But, if you get a method not found error, it's probably
because the form is using an ADO recordset instead of a DAO
recordset. ADO uses the Find method instead of FindFirst.

OTOH, your main form should not be loading all the customer
records, which would be a gross waste of resources. Instead
the form should open with no customer records and only load
one customer record after a selection is made in the combo
box.

Ok. That's not what I was trying to accomplish. I will try to be a
little more clear (sorry). I have multiple customers (which I do want
loaded on the mainform (customers) when it opens, there are never more
than 50 customers). Each customer is automatically assigned a
customer ID (which he never sees-it is an autonumber that is
irrelivant to the user, just there because there has to be a primary
key). This customer ID is automatically propogated to the linked
subform (service requests) for every record for that customer. In the
subform there is a txtbox that holds a ticket ID (which is not
necessarily unique, and each customer has multiple ticket IDs). I
have also added an embedded subform (just there so he can see the
ticket IDs and is not necessary) that shows all of the ticket IDs for
the currently displayed customer. I have created a combobox that is
on the mainform that is autopopulated with all of the ticket IDs for
ALL customers. I would for him to be able to select a ticket ID (or
type it in) and have the mainform go to the next customer that
contains that ticket ID in one of it's records in the subform.

Now I am even more confused, because that's what I thought
you wanted and what I intended to happen when a user
selected a ticket in the combo box.

Can you explain what you did, what effect it produced and
what happened?

--
Marsh- Hide quoted text -

- Show quoted text -

I'm sorry. Marshall, your post worked. The only thing I can see that
doesn't work now, is that if there are multiple instances of one
ticket ID, it goes to the first one it finds, but won't go to the next
one if the first one it finds isn't the correct instance of it. For
instance, if Mike and Tom both have ticket ID 13225, and I type 13225
in the combo box, it will go to Mike (customer record). If I type it
again and hit enter, it stays on Mike. I can click the drop down,
find the two instances of 13225 and click on both of them and it will
take me to both Tom and Mike's accounts (when the correct one is
clicked on) but you never know which 13225 belongs to which customer.
Is there a way to make it go to the next customer containing 13225 if
needed?

THANK YOU SOOOO MUCH FOR YOUR HELP. WHAT YOU HAVE GIVEN ME SO FAR IS
INCREDIBLE!!!!

[Marshall Barton]

Robert Jacobs wrote:

>I'm sorry. Marshall, your post worked. The only thing I can see that
doesn't work now, is that if there are multiple instances of one
ticket ID, it goes to the first one it finds, but won't go to the next
one if the first one it finds isn't the correct instance of it. For
instance, if Mike and Tom both have ticket ID 13225, and I type 13225
in the combo box, it will go to Mike (customer record). If I type it
again and hit enter, it stays on Mike. I can click the drop down,
find the two instances of 13225 and click on both of them and it will
take me to both Tom and Mike's accounts (when the correct one is
clicked on) but you never know which 13225 belongs to which customer.
Is there a way to make it go to the next customer containing 13225 if
needed?

How about just adding the customer name to the combo box so
you can see which one you are selecting?

If you want some other mechanism to navigate between
customers with the same ticket number, then try using
buttons with the FindNext and FindPrevious methods. E.g.

Sub btnNextCust_Click()
Dim varCurrRec As Variant
strCurrRec = Me.Bookmark
Me.Recordset.FindNext "[customer ID]=" & Me.combobox
If Me.Recordset.NoMatch Then
Beep
Me.Bookmark = varCurrRec
End If
End Sub

Similarly for the btnPrevCust button.

--
Marsh

[Robert Jacobs]

On Apr 11, 1:42 pm, Marshall Barton <[e-mail]:

Robert Jacobs wrote:

I'm sorry. Marshall, your post worked. The only thing I can see that
doesn't work now, is that if there are multiple instances of one
ticket ID, it goes to the first one it finds, but won't go to the next
one if the first one it finds isn't the correct instance of it. For
instance, if Mike and Tom both have ticket ID 13225, and I type 13225
in the combo box, it will go to Mike (customer record). If I type it
again and hit enter, it stays on Mike. I can click the drop down,
find the two instances of 13225 and click on both of them and it will
take me to both Tom and Mike's accounts (when the correct one is
clicked on) but you never know which 13225 belongs to which customer.
Is there a way to make it go to the next customer containing 13225 if
needed?

How about just adding the customer name to the combo box so
you can see which one you are selecting?

If you want some other mechanism to navigate between
customers with the same ticket number, then try using
buttons with the FindNext and FindPrevious methods. E.g.

Sub btnNextCust_Click()
Dim varCurrRec As Variant
strCurrRec = Me.Bookmark
Me.Recordset.FindNext "[customer ID]=" & Me.combobox
If Me.Recordset.NoMatch Then
Beep
Me.Bookmark = varCurrRec
End If
End Sub

Similarly for the btnPrevCust button.

--
Marsh

I would actually like to add the name of the customer to the combobox
(great idea). Here's what I have:

Row Source: SELECT DISTINCT [Service Records].[Ticket ID] & " " &
Customers.contactfirstname & " " & customers.contactlastname, [Service
Records].CustomerID FROM [Service Records];

This does add the customers contact first name and customers contact
last name to the combo box, but it puts the name for the currently
displayed customer next to all of the ticket IDs (even the ticket IDs
not belonging to the currently selected custoemr). What have I done
wrong?

[Marshall Barton]

>I would actually like to add the name of the customer to the combobox

>(great idea). Here's what I have:

Row Source: SELECT DISTINCT [Service Records].[Ticket ID] & " " &
Customers.contactfirstname & " " & customers.contactlastname, [Service
Records].CustomerID FROM [Service Records];

This does add the customers contact first name and customers contact
last name to the combo box, but it puts the name for the currently
displayed customer next to all of the ticket IDs (even the ticket IDs
not belonging to the currently selected custoemr). What have I done
wrong?

What?? How can the tickets subform display something from
the mainform?

Where is this combo box? It sounds like it's in the
subform's detail section. I thought it was in the main
form's header section, did I misunderstand what your were
describing?

--
Marsh

[Robert Jacobs]

On Apr 11, 6:24 pm, Marshall Barton <[e-mail]:

I would actually like to add the name of the customer to the combobox
(great idea). Here's what I have:

Row Source: SELECT DISTINCT [Service Records].[Ticket ID] & " " &
Customers.contactfirstname & " " & customers.contactlastname, [Service
Records].CustomerID FROM [Service Records];

This does add the customers contact first name and customers contact
last name to the combo box, but it puts the name for the currently
displayed customer next to all of the ticket IDs (even the ticket IDs
not belonging to the currently selected custoemr). What have I done
wrong?

What?? How can the tickets subform display something from
the mainform?

Where is this combo box? It sounds like it's in the
subform's detail section. I thought it was in the main
form's header section, did I misunderstand what your were
describing?

--
Marsh

Marsh,

The combo box is in the mainform's (customers) header section. When
on the mainform, and a customer is displayed as the current record,
clicking the combobox shows all of the ticket IDs, but it has the name
of the currently selected customer next to each ticket ID.

[Marshall Barton]

Robert Jacobs wrote:

>The combo box is in the mainform's (customers) header section. When
on the mainform, and a customer is displayed as the current record,
clicking the combobox shows all of the ticket IDs, but it has the name
of the currently selected customer next to each ticket ID.

Let's try this again. I am lost as to what "it" you are
referring to that has the customer name. The combo box will
of course display the customer name because that's what you
wanted it to do. AFAIK, the only other "it" that displays a
ticket ID is the subform and I don't see how the combo box
can affect a control in the subform.

Maybe you should try a slightly different approach. Change
the combo box's Row Source to:

SELECT DISTINCT [Ticket ID],
contactfirstname & " " & contactlastname As CN,
CustomerID
FROM [Service Records]

And change the combo box's ColumnCount to 3 and ColumnWidth
to something like .6;1.3;0
Then see what effect you get.

--
Marsh

[Robert Jacobs]

On Apr 12, 12:43 pm, Marshall Barton <[e-mail]:

Robert Jacobs wrote:

The combo box is in the mainform's (customers) header section. When
on the mainform, and a customer is displayed as the current record,
clicking the combobox shows all of the ticket IDs, but it has the name
of the currently selected customer next to each ticket ID.

Let's try this again. I am lost as to what "it" you are
referring to that has the customer name. The combo box will
of course display the customer name because that's what you
wanted it to do. AFAIK, the only other "it" that displays a
ticket ID is the subform and I don't see how the combo box
can affect a control in the subform.

Maybe you should try a slightly different approach. Change
the combo box's Row Source to:

SELECT DISTINCT [Ticket ID],
contactfirstname & " " & contactlastname As CN,
CustomerID
FROM [Service Records]

And change the combo box's ColumnCount to 3 and ColumnWidth
to something like .6;1.3;0
Then see what effect you get.

--
Marsh

Marsh, Thanks for the reply. I entered the Row Source as listed
above, and the results are the same as I was experiencing before.
I'll explain them more clearly:

Scenario: I am on the mainform (customers) and am looking at customer
Bobby Fink. If I click the combobox, I see all three tickets with
Bobby Fink's name next to it. Like this:
1248 Bobby Fink
1248 Bobby Fink
5263 Bobby Fink

If I change the mainform (customers) to view a different customer,
such as Nick Klein, I see this when I click the combobox
1248 Nick Klein
1248 Nick Klein
5263 Nick Klein

(The reason for having 2 ticket IDs of 1248 is to test that the
correct name shows up by each ticket ID, but it obviously doesn't) I
hope this explains my situation a little better. Thanks again for
your great help!

[Robert Jacobs]

On Apr 12, 12:43 pm, Marshall Barton <[e-mail]:

Robert Jacobs wrote:

The combo box is in the mainform's (customers) header section. When
on the mainform, and a customer is displayed as the current record,
clicking the combobox shows all of the ticket IDs, but it has the name
of the currently selected customer next to each ticket ID.

Let's try this again. I am lost as to what "it" you are
referring to that has the customer name. The combo box will
of course display the customer name because that's what you
wanted it to do. AFAIK, the only other "it" that displays a
ticket ID is the subform and I don't see how the combo box
can affect a control in the subform.

Maybe you should try a slightly different approach. Change
the combo box's Row Source to:

SELECT DISTINCT [Ticket ID],
contactfirstname & " " & contactlastname As CN,
CustomerID
FROM [Service Records]

And change the combo box's ColumnCount to 3 and ColumnWidth
to something like .6;1.3;0
Then see what effect you get.

--
Marsh

OK, by "it" I meant the combo box. Instead of showing everything in
multiple columns like you have suggested, I was showing them all in
one column (them is TicketID and Customer First and Last name). I
have implemented the code you suggested above and I get the same
result...which is:

Scenario. I have three customers. Bobby Fink, Nick Klein, and Bernie
Teplan. Each of these customers has one ticket ID assigned to them.
Bobby Fink has ticket ID 1287, Nick Klein also has ticket ID 1287
(which is a different service request, just here for testing
purposes), and Bernie Teplan has ticket ID 5545.

If the mainform is currently displaying Bobby Fink (as the customer)
and I click the combo box, here's what I get in the drop down:
Bobby Fink 1287
Bobby Fink 1287
Bobby Fink 5545

If I then switch to Nick Klein on the mainform and click the combo
box, I see this:
Nick Klein 1287
Nick Klein 1287
Nick Klein 5545

Finally, If I switch to Bernie Teplan and click the combo box, I get
this result:
Bernie Teplan 1287
Bernie Teplan 1287
Bernie Teplan 5545

Thank you again for your help, I just wanted you to know what exactly
is happening as it is hard to explain (at least for me) any other way.

[Robert Jacobs]

On Apr 12, 12:43 pm, Marshall Barton <[e-mail]:

Robert Jacobs wrote:

The combo box is in the mainform's (customers) header section. When
on the mainform, and a customer is displayed as the current record,
clicking the combobox shows all of the ticket IDs, but it has the name
of the currently selected customer next to each ticket ID.

Let's try this again. I am lost as to what "it" you are
referring to that has the customer name. The combo box will
of course display the customer name because that's what you
wanted it to do. AFAIK, the only other "it" that displays a
ticket ID is the subform and I don't see how the combo box
can affect a control in the subform.

Maybe you should try a slightly different approach. Change
the combo box's Row Source to:

SELECT DISTINCT [Ticket ID],
contactfirstname & " " & contactlastname As CN,
CustomerID
FROM [Service Records]

And change the combo box's ColumnCount to 3 and ColumnWidth
to something like .6;1.3;0
Then see what effect you get.

--
Marsh

I use the web version of google groups. I believe I posted twice my
last response, but don't see either in the web interface. Just in
case I am going to create a new post, because I really would like more
help. If you could respond to the new post I would appreciate it!

[Robert Jacobs]

On Apr 12, 4:03 pm, "Robert Jacobs" <[e-mail]:

On Apr 12, 12:43 pm, Marshall Barton <[e-mail]:

Robert Jacobs wrote:

>The combo box is in the mainform's (customers) header section. When
>on the mainform, and a customer is displayed as the current record,
>clicking the combobox shows all of the ticket IDs, but it has the name
>of the currently selected customer next to each ticket ID.

Let's try this again. I am lost as to what "it" you are
referring to that has the customer name. The combo box will
of course display the customer name because that's what you
wanted it to do. AFAIK, the only other "it" that displays a
ticket ID is the subform and I don't see how the combo box
can affect a control in the subform.

Maybe you should try a slightly different approach. Change
the combo box's Row Source to:

SELECT DISTINCT [Ticket ID],
contactfirstname & " " & contactlastname As CN,
CustomerID
FROM [Service Records]

And change the combo box's ColumnCount to 3 and ColumnWidth
to something like .6;1.3;0
Then see what effect you get.

--
Marsh

I use the web version of google groups. I believe I posted twice my
last response, but don't see either in the web interface. Just in
case I am going to create a new post, because I really would like more
help. If you could respond to the new post I would appreciate it!- Hide quoted text -

- Show quoted text -

Sorry for the confusing posts. I finally resolved my issue!!!! I
wasn't thinking about how the sql statements we were writing were
working. Because the customer name isn't in the subform, I had to
inner join the mainform to the statement. Confusing? Here's what I
changed the row source to:

SELECT DISTINCT [Ticket ID], contactfirstname & " " & contactlastname
AS CN, a.CustomerID FROM [Service Records] AS a INNER JOIN customers
AS b ON a.CustomerID=b.CustomerID;

Thanks for all of your help Marshall! You really helped me not give
up by giving me glimpses of hope. Thanks again. I have never had a
bad experience working with any of the MVPs, and your 5 star rating
shows that there won't be an exception to that any time soon. Sorry
if I was a paing to understand and help. I'm very greatful, however,
for all of your patience and advice!

[Marshall Barton]

Robert Jacobs wrote:

>Sorry for the confusing posts. I finally resolved my issue!!!! I
wasn't thinking about how the sql statements we were writing were
working. Because the customer name isn't in the subform, I had to
inner join the mainform to the statement. Confusing? Here's what I
changed the row source to:

SELECT DISTINCT [Ticket ID], contactfirstname & " " & contactlastname
AS CN, a.CustomerID FROM [Service Records] AS a INNER JOIN customers
AS b ON a.CustomerID=b.CustomerID;

I noticed something funny in the query before, but forgot to
ask you how the names got to be in the the data. I still
don't understand why the query did what it did without
generating some kind of error or at least prompting for the
first and last names.

>Thanks for all of your help Marshall! You really helped me not give
up by giving me glimpses of hope. Thanks again. I have never had a
bad experience working with any of the MVPs, and your 5 star rating
shows that there won't be an exception to that any time soon. Sorry
if I was a paing to understand and help. I'm very greatful, however,
for all of your patience and advice!

You're welcome. Glad you were able to work through the
problem.

--
Marsh