Sync Subform with Mainform

[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

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]

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

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]

Re: Sync Subform with Mainform

On Apr 12, 4:03 pm, "Robert Jacobs" <robertjacob... @gmail.comwrote :

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

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

Re: Sync Subform with Mainform

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