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]

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]

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]

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]

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]

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]

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]

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]

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]

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