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.Fi ndFirst "[Serial]=" & Me.combobox


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

[lesperancer@natpro.com]

Re: Sync Subform with Mainform

On Apr 9, 10:08 am, "Robert Jacobs" <robertjacob... @gmail.comwrote :

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.Fi ndFirst "[Serial]=" & Me.combobox
>
If serial is a Text field:
Me.Recordset.Fi ndFirst "[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]

Re: Sync Subform with Mainform

On Apr 9, 11:34 am, lesperan...@nat pro.com wrote:

On Apr 9, 10:08 am, "Robert Jacobs" <robertjacob... @gmail.comwrote :
>
>
>
>
>

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.Fi ndFirst "[Serial]=" & Me.combobox

>

If serial is a Text field:
Me.Recordset.Fi ndFirst "[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]

Re: Sync Subform with Mainform

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.Fi ndFirst "[Serial]=" & Me.combobox
>
>
>If serial is a Text field:
Me.Recordset.Fi ndFirst "[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.Fi ndFirst "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]

Re: Sync Subform with Mainform

On Apr 10, 2:45 pm, Marshall Barton <marshbar...@wo wway.comwrote:

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.Fi ndFirst "[Serial]=" & Me.combobox

>

If serial is a Text field:
Me.Recordset.Fi ndFirst "[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.Fi ndFirst "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@natpro.com]

Re: Sync Subform with Mainform

On Apr 10, 2:07 pm, "Robert Jacobs" <robertjacob... @gmail.comwrote :

On Apr 10, 2:45 pm, Marshall Barton <marshbar...@wo wway.comwrote:
>
>
>
>
>

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.Fi ndFirst "[Serial]=" & Me.combobox

>

>If serial is a Text field:
Me.Recordset.Fi ndFirst "[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.Fi ndFirst "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.Fi ndFirst "[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]

Re: Sync Subform with Mainform

On Apr 10, 3:42 pm, lesperan...@nat pro.com wrote:

On Apr 10, 2:07 pm, "Robert Jacobs" <robertjacob... @gmail.comwrote :
>
>
>
>
>

On Apr 10, 2:45 pm, Marshall Barton <marshbar...@wo wway.comwrote:

>

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.Fi ndFirst "[Serial]=" & Me.combobox

>

If serial is a Text field:
Me.Recordset.Fi ndFirst "[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.Fi ndFirst "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.Fi ndFirst "[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]

Re: Sync Subform with Mainform

Robert Jacobs wrote:

>On Apr 10, 2:45 pm, Marshall Barton <marshbar...@wo wway.comwrote:

>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.Fi ndFirst "[Serial]=" & Me.combobox

>>

>If serial is a Text field:
Me.Recordset.Fi ndFirst "[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.F indFirst "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]

Re: Sync Subform with Mainform

On Apr 10, 3:59 pm, Marshall Barton <marshbar...@wo wway.comwrote:

Robert Jacobs wrote:

On Apr 10, 2:45 pm, Marshall Barton <marshbar...@wo wway.comwrote:

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.Fi ndFirst "[Serial]=" & Me.combobox

>

If serial is a Text field:
Me.Recordset.Fi ndFirst "[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.Fi ndFirst "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]

Re: Sync Subform with Mainform

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_Cli ck()
Dim varCurrRec As Variant
strCurrRec = Me.Bookmark
Me.Recordset.Fi ndNext "[customer ID]=" & Me.combobox
If Me.Recordset.No Match Then
Beep
Me.Bookmark = varCurrRec
End If
End Sub

Similarly for the btnPrevCust button.

--
Marsh

[Robert Jacobs]

Re: Sync Subform with Mainform

On Apr 11, 1:42 pm, Marshall Barton <marshbar...@wo wway.comwrote:

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_Cli ck()
Dim varCurrRec As Variant
strCurrRec = Me.Bookmark
Me.Recordset.Fi ndNext "[customer ID]=" & Me.combobox
If Me.Recordset.No Match 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.conta ctfirstname & " " & customers.conta ctlastname, [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]

Re: Sync Subform with Mainform

>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.cont actfirstname & " " & customers.conta ctlastname, [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]

Re: Sync Subform with Mainform

On Apr 11, 6:24 pm, Marshall Barton <marshbar...@wo wway.comwrote:

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.conta ctfirstname & " " & customers.conta ctlastname, [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]

Re: Sync Subform with Mainform

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],
contactfirstnam e & " " & 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]

Re: Sync Subform with Mainform

On Apr 12, 12:43 pm, Marshall Barton <marshbar...@wo wway.comwrote:

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],
contactfirstnam e & " " & 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!