Join Query

E

Esaw

I am trying to create a query that displays data from a combo box and
autofill in a subform (I hope that makes sense) But when I set up the query
it shows no data. I was told to link the two tables in the query design. I
did and it is now pulling the correct data but the field that uses the
comobox in the subform is showing the ID number (PrimaryKey) and not the
StyleNumber as I thought it should. Here is the SQL for the join that is
working to this point. If anyone could help me out on this I'd appreciate.
I'm sure it's set up wrong, but I'm pretty new to Access and all it's fun
ways!
Thanks
Eisa

SELECT Contracts.ArtistName, Contracts.StyleNumber,
Contracts.StyleDescription, Contracts.ClientName, Contracts.ContractStart,
Contracts.ContractEnd, Contracts.RoyaltyRate, Contracts.Product
FROM Art RIGHT JOIN Contracts ON (Art.StyleNumber = Contracts.StyleNumber)
AND (Art.StyleDescription = Contracts.StyleDescription)
ORDER BY Contracts.StyleNumber, Contracts.ContractStart DESC;
 
M

Michel Walsh

The combo box can display more than one column. Check the properties:
Column Count and Column Widths. It seems you have 8 columns. To NOT
display a given column the query returns, give it a width of 0.


Vanderghast, Access MVP
 
E

Esaw

Thanks for replying. I've actually got 3 columns in the combo box. Column
widths are 0", .5", 0" The second is the column that I want to display in the
query. Bound column is 1 and below is the Row source for the combo box:

SELECT [Art].[ID], [Art].[StyleNumber], [Art].[StyleDescription] FROM Art
ORDER BY [StyleNumber];

Control Source for autofill TextBox:
=[ArtID].[Column](2)

Do you see anything in this set up that could cause the query not to display
the correct data?

Thanks
 
M

Michel Walsh

One way to push data from a combo box to an edit box is to write the
following code in the procedure handling the After Change event of the combo
box. Something like:


Private Sub ComboBoxName_AfterUpdate()

Me.EditTextBoxControl = Me.ComboBoxName.Column(2)

End Sub



Hoping it may help,
Vanderghast, Access MVP



Esaw said:
Thanks for replying. I've actually got 3 columns in the combo box. Column
widths are 0", .5", 0" The second is the column that I want to display in
the
query. Bound column is 1 and below is the Row source for the combo box:

SELECT [Art].[ID], [Art].[StyleNumber], [Art].[StyleDescription] FROM Art
ORDER BY [StyleNumber];

Control Source for autofill TextBox:
=[ArtID].[Column](2)

Do you see anything in this set up that could cause the query not to
display
the correct data?

Thanks

Michel Walsh said:
The combo box can display more than one column. Check the properties:
Column Count and Column Widths. It seems you have 8 columns. To NOT
display a given column the query returns, give it a width of 0.


Vanderghast, Access MVP
Click to expand...
Click to expand...
 
E

Esaw

Still not displaying the StyleNumber. Here's the code I put into the
AfterUpdate event

Private Sub ArtID_AfterUpdate()

Me.EditTextBoxControl = Me.ArtID.Column(2)

End Sub


Michel Walsh said:
One way to push data from a combo box to an edit box is to write the
following code in the procedure handling the After Change event of the combo
box. Something like:


Private Sub ComboBoxName_AfterUpdate()

Me.EditTextBoxControl = Me.ComboBoxName.Column(2)

End Sub



Hoping it may help,
Vanderghast, Access MVP



Esaw said:
Thanks for replying. I've actually got 3 columns in the combo box. Column
widths are 0", .5", 0" The second is the column that I want to display in
the
query. Bound column is 1 and below is the Row source for the combo box:

SELECT [Art].[ID], [Art].[StyleNumber], [Art].[StyleDescription] FROM Art
ORDER BY [StyleNumber];

Control Source for autofill TextBox:
=[ArtID].[Column](2)

Do you see anything in this set up that could cause the query not to
display
the correct data?

Thanks

Michel Walsh said:
The combo box can display more than one column. Check the properties:
Column Count and Column Widths. It seems you have 8 columns. To NOT
display a given column the query returns, give it a width of 0.


Vanderghast, Access MVP


I am trying to create a query that displays data from a combo box and
autofill in a subform (I hope that makes sense) But when I set up the
query
it shows no data. I was told to link the two tables in the query
design. I
did and it is now pulling the correct data but the field that uses the
comobox in the subform is showing the ID number (PrimaryKey) and not
the
StyleNumber as I thought it should. Here is the SQL for the join that
is
working to this point. If anyone could help me out on this I'd
appreciate.
I'm sure it's set up wrong, but I'm pretty new to Access and all it's
fun
ways!
Thanks
Eisa

SELECT Contracts.ArtistName, Contracts.StyleNumber,
Contracts.StyleDescription, Contracts.ClientName,
Contracts.ContractStart,
Contracts.ContractEnd, Contracts.RoyaltyRate, Contracts.Product
FROM Art RIGHT JOIN Contracts ON (Art.StyleNumber =
Contracts.StyleNumber)
AND (Art.StyleDescription = Contracts.StyleDescription)
ORDER BY Contracts.StyleNumber, Contracts.ContractStart DESC;
Click to expand...
Click to expand...
Click to expand...
 
M

Michel Walsh

The name of the edit text control is EditTextBoxControl? and the combo box
has 3 columns?


Also, your edit text box control should not be bound to a computed
expression.


Vanderghast, Access MVP



Esaw said:
Still not displaying the StyleNumber. Here's the code I put into the
AfterUpdate event

Private Sub ArtID_AfterUpdate()

Me.EditTextBoxControl = Me.ArtID.Column(2)

End Sub


Michel Walsh said:
One way to push data from a combo box to an edit box is to write the
following code in the procedure handling the After Change event of the
combo
box. Something like:


Private Sub ComboBoxName_AfterUpdate()

Me.EditTextBoxControl = Me.ComboBoxName.Column(2)

End Sub



Hoping it may help,
Vanderghast, Access MVP



Esaw said:
Thanks for replying. I've actually got 3 columns in the combo box.
Column
widths are 0", .5", 0" The second is the column that I want to display
in
the
query. Bound column is 1 and below is the Row source for the combo box:

SELECT [Art].[ID], [Art].[StyleNumber], [Art].[StyleDescription] FROM
Art
ORDER BY [StyleNumber];

Control Source for autofill TextBox:
=[ArtID].[Column](2)

Do you see anything in this set up that could cause the query not to
display
the correct data?

Thanks

:

The combo box can display more than one column. Check the properties:
Column Count and Column Widths. It seems you have 8 columns. To
NOT
display a given column the query returns, give it a width of 0.


Vanderghast, Access MVP


I am trying to create a query that displays data from a combo box and
autofill in a subform (I hope that makes sense) But when I set up
the
query
it shows no data. I was told to link the two tables in the query
design. I
did and it is now pulling the correct data but the field that uses
the
comobox in the subform is showing the ID number (PrimaryKey) and not
the
StyleNumber as I thought it should. Here is the SQL for the join
that
is
working to this point. If anyone could help me out on this I'd
appreciate.
I'm sure it's set up wrong, but I'm pretty new to Access and all
it's
fun
ways!
Thanks
Eisa

SELECT Contracts.ArtistName, Contracts.StyleNumber,
Contracts.StyleDescription, Contracts.ClientName,
Contracts.ContractStart,
Contracts.ContractEnd, Contracts.RoyaltyRate, Contracts.Product
FROM Art RIGHT JOIN Contracts ON (Art.StyleNumber =
Contracts.StyleNumber)
AND (Art.StyleDescription = Contracts.StyleDescription)
ORDER BY Contracts.StyleNumber, Contracts.ContractStart DESC;
Click to expand...
Click to expand...
Click to expand...
 
E

Esaw

Oops...I changed it to:
Private Sub ArtID_AfterUpdate()

Me.StyleDescription = Me.ArtID.Column(2)

End Sub

But it's still not working. and yes the Column Count of the Combo Box is 3

Michel Walsh said:
The name of the edit text control is EditTextBoxControl? and the combo box
has 3 columns?


Also, your edit text box control should not be bound to a computed
expression.


Vanderghast, Access MVP



Esaw said:
Still not displaying the StyleNumber. Here's the code I put into the
AfterUpdate event

Private Sub ArtID_AfterUpdate()

Me.EditTextBoxControl = Me.ArtID.Column(2)

End Sub


Michel Walsh said:
One way to push data from a combo box to an edit box is to write the
following code in the procedure handling the After Change event of the
combo
box. Something like:


Private Sub ComboBoxName_AfterUpdate()

Me.EditTextBoxControl = Me.ComboBoxName.Column(2)

End Sub



Hoping it may help,
Vanderghast, Access MVP



Thanks for replying. I've actually got 3 columns in the combo box.
Column
widths are 0", .5", 0" The second is the column that I want to display
in
the
query. Bound column is 1 and below is the Row source for the combo box:

SELECT [Art].[ID], [Art].[StyleNumber], [Art].[StyleDescription] FROM
Art
ORDER BY [StyleNumber];

Control Source for autofill TextBox:
=[ArtID].[Column](2)

Do you see anything in this set up that could cause the query not to
display
the correct data?

Thanks

:

The combo box can display more than one column. Check the properties:
Column Count and Column Widths. It seems you have 8 columns. To
NOT
display a given column the query returns, give it a width of 0.


Vanderghast, Access MVP


I am trying to create a query that displays data from a combo box and
autofill in a subform (I hope that makes sense) But when I set up
the
query
it shows no data. I was told to link the two tables in the query
design. I
did and it is now pulling the correct data but the field that uses
the
comobox in the subform is showing the ID number (PrimaryKey) and not
the
StyleNumber as I thought it should. Here is the SQL for the join
that
is
working to this point. If anyone could help me out on this I'd
appreciate.
I'm sure it's set up wrong, but I'm pretty new to Access and all
it's
fun
ways!
Thanks
Eisa

SELECT Contracts.ArtistName, Contracts.StyleNumber,
Contracts.StyleDescription, Contracts.ClientName,
Contracts.ContractStart,
Contracts.ContractEnd, Contracts.RoyaltyRate, Contracts.Product
FROM Art RIGHT JOIN Contracts ON (Art.StyleNumber =
Contracts.StyleNumber)
AND (Art.StyleDescription = Contracts.StyleDescription)
ORDER BY Contracts.StyleNumber, Contracts.ContractStart DESC;
Click to expand...
Click to expand...
Click to expand...
 
E

Esaw

Now I'm lost. I don't know what a debug stop point is. And the code that's
written below I have already. Could you please re-explain what I should try?
Thanks
Eisa
 
M

Michel Walsh

You have open the attachment? It shows a debug-stop point (the red dot on
the left gray bar) You can set/reset a debug stop point by clicking on the
gray bar, at the line where the computer is to stop. Then, running the
application, when the computer got to that line, BEFORE executing it, it
stops (which, in the attachment is indicated by the yellow arrow). You can
then investigate what is the value of your variable and expression, by
hovering your mouse cursor over it.


Vanderghast, Access MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query and auto fill 3
Sorting in Query and Report involving look up combo box 1
Query/Table issue 0
Join Query that needs more included 14
perform INNER JOIN on query results 2
Full Join Query/Union 4
Left join in query 14
How do you ignore entire tables in an Inner Join Query? 16

Top