form combo box displayed record

G

Guest

I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
 
G

Guest

When you say "All data shows except the combo box", do you mean the combobox
doesn't have any data in it's dropdown portion or that the value from the
form's recordsource is not displaying in the combobox?

Barry
 
G

Guest

The value from the form's record source isn't showing. I have data in the
dropdown portion.

Barry Gilbert said:
When you say "All data shows except the combo box", do you mean the combobox
doesn't have any data in it's dropdown portion or that the value from the
form's recordsource is not displaying in the combobox?

Barry

fsuds said:
I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
Click to expand...
Click to expand...
 
G

Guest

You need to look at the Bound Column property of the combobox. The values in
this column needs to be the same data you are referencing in the
ControlSource property. If you are displaying concatenated data but want to
bind to a single value, create a hidden column and bind to it. You can hide a
column by setting it's width to 0 in the ColumnWidths property.

Barry

fsuds said:
The value from the form's record source isn't showing. I have data in the
dropdown portion.

Barry Gilbert said:
When you say "All data shows except the combo box", do you mean the combobox
doesn't have any data in it's dropdown portion or that the value from the
form's recordsource is not displaying in the combobox?

Barry

fsuds said:
I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I actually did that. Here is the row source for my combo. Control Source is
EVENTID.

SELECT DISTINCT [EVENT].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER] AS [Event Name and Filenumber], [EVENT].[Active] FROM
EVENT LEFT JOIN RENTAL ON [EVENT].[EVENTID]=[RENTAL].[EventID] WHERE
((([EVENT].[Active])=-1));

Barry Gilbert said:
You need to look at the Bound Column property of the combobox. The values in
this column needs to be the same data you are referencing in the
ControlSource property. If you are displaying concatenated data but want to
bind to a single value, create a hidden column and bind to it. You can hide a
column by setting it's width to 0 in the ColumnWidths property.

Barry

fsuds said:
The value from the form's record source isn't showing. I have data in the
dropdown portion.

Barry Gilbert said:
When you say "All data shows except the combo box", do you mean the combobox
doesn't have any data in it's dropdown portion or that the value from the
form's recordsource is not displaying in the combobox?

Barry

:

I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Ok. Is the Bound Column property set to 1? If so, this should work.

fsuds said:
I actually did that. Here is the row source for my combo. Control Source is
EVENTID.

SELECT DISTINCT [EVENT].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER] AS [Event Name and Filenumber], [EVENT].[Active] FROM
EVENT LEFT JOIN RENTAL ON [EVENT].[EVENTID]=[RENTAL].[EventID] WHERE
((([EVENT].[Active])=-1));

Barry Gilbert said:
You need to look at the Bound Column property of the combobox. The values in
this column needs to be the same data you are referencing in the
ControlSource property. If you are displaying concatenated data but want to
bind to a single value, create a hidden column and bind to it. You can hide a
column by setting it's width to 0 in the ColumnWidths property.

Barry

fsuds said:
The value from the form's record source isn't showing. I have data in the
dropdown portion.

:

When you say "All data shows except the combo box", do you mean the combobox
doesn't have any data in it's dropdown portion or that the value from the
form's recordsource is not displaying in the combobox?

Barry

:

I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes. If the col width is set to 0 nothing shows, however, if it is set to
anything above that, col 1 shows up in the cmb.

Barry Gilbert said:
Ok. Is the Bound Column property set to 1? If so, this should work.

fsuds said:
I actually did that. Here is the row source for my combo. Control Source is
EVENTID.

SELECT DISTINCT [EVENT].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER] AS [Event Name and Filenumber], [EVENT].[Active] FROM
EVENT LEFT JOIN RENTAL ON [EVENT].[EVENTID]=[RENTAL].[EventID] WHERE
((([EVENT].[Active])=-1));

Barry Gilbert said:
You need to look at the Bound Column property of the combobox. The values in
this column needs to be the same data you are referencing in the
ControlSource property. If you are displaying concatenated data but want to
bind to a single value, create a hidden column and bind to it. You can hide a
column by setting it's width to 0 in the ColumnWidths property.

Barry

:

The value from the form's record source isn't showing. I have data in the
dropdown portion.

:

When you say "All data shows except the combo box", do you mean the combobox
doesn't have any data in it's dropdown portion or that the value from the
form's recordsource is not displaying in the combobox?

Barry

:

I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Not talking about column widths. Look at the BoundColumn property on the Data
property sheet. Is this set to 1?

fsuds said:
Yes. If the col width is set to 0 nothing shows, however, if it is set to
anything above that, col 1 shows up in the cmb.

Barry Gilbert said:
Ok. Is the Bound Column property set to 1? If so, this should work.

fsuds said:
I actually did that. Here is the row source for my combo. Control Source is
EVENTID.

SELECT DISTINCT [EVENT].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER] AS [Event Name and Filenumber], [EVENT].[Active] FROM
EVENT LEFT JOIN RENTAL ON [EVENT].[EVENTID]=[RENTAL].[EventID] WHERE
((([EVENT].[Active])=-1));

:

You need to look at the Bound Column property of the combobox. The values in
this column needs to be the same data you are referencing in the
ControlSource property. If you are displaying concatenated data but want to
bind to a single value, create a hidden column and bind to it. You can hide a
column by setting it's width to 0 in the ColumnWidths property.

Barry

:

The value from the form's record source isn't showing. I have data in the
dropdown portion.

:

When you say "All data shows except the combo box", do you mean the combobox
doesn't have any data in it's dropdown portion or that the value from the
form's recordsource is not displaying in the combobox?

Barry

:

I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Yes. Bound column is one.

Barry Gilbert said:
Not talking about column widths. Look at the BoundColumn property on the Data
property sheet. Is this set to 1?

fsuds said:
Yes. If the col width is set to 0 nothing shows, however, if it is set to
anything above that, col 1 shows up in the cmb.

Barry Gilbert said:
Ok. Is the Bound Column property set to 1? If so, this should work.

:

I actually did that. Here is the row source for my combo. Control Source is
EVENTID.

SELECT DISTINCT [EVENT].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER] AS [Event Name and Filenumber], [EVENT].[Active] FROM
EVENT LEFT JOIN RENTAL ON [EVENT].[EVENTID]=[RENTAL].[EventID] WHERE
((([EVENT].[Active])=-1));

:

You need to look at the Bound Column property of the combobox. The values in
this column needs to be the same data you are referencing in the
ControlSource property. If you are displaying concatenated data but want to
bind to a single value, create a hidden column and bind to it. You can hide a
column by setting it's width to 0 in the ColumnWidths property.

Barry

:

The value from the form's record source isn't showing. I have data in the
dropdown portion.

:

When you say "All data shows except the combo box", do you mean the combobox
doesn't have any data in it's dropdown portion or that the value from the
form's recordsource is not displaying in the combobox?

Barry

:

I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I've set up some code trying to get the cbo to display a record but it only
displays my SQL statement. How do I get the statement to run and then my cbo
to disply the results? Here is the code:

Dim strSQL As String

strSQL = "Select ([EVENT].[NAME])&', '&([EVENT].[FILENUMBER])" & _
" From EVENT" & _
" WHERE [EVENT].[EVENTID] = forms![frmEditRental].[txtEventID].Value"

Me.cmbEvent = strSQL
 
G

Guest

C'mon... anyone??? I've been going at this for a month now.

fsuds said:
I've set up some code trying to get the cbo to display a record but it only
displays my SQL statement. How do I get the statement to run and then my cbo
to disply the results? Here is the code:

Dim strSQL As String

strSQL = "Select ([EVENT].[NAME])&', '&([EVENT].[FILENUMBER])" & _
" From EVENT" & _
" WHERE [EVENT].[EVENTID] = forms![frmEditRental].[txtEventID].Value"

Me.cmbEvent = strSQL

fsuds said:
I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
Click to expand...
Click to expand...
 
G

Guest

Nevermind, I'm such an idiot!!! I have WHERE Active=-1 so anything that is
not marked active will not show up... DUH!!! thanks.

fsuds said:
Yes. Bound column is one.

Barry Gilbert said:
Not talking about column widths. Look at the BoundColumn property on the Data
property sheet. Is this set to 1?

fsuds said:
Yes. If the col width is set to 0 nothing shows, however, if it is set to
anything above that, col 1 shows up in the cmb.

:

Ok. Is the Bound Column property set to 1? If so, this should work.

:

I actually did that. Here is the row source for my combo. Control Source is
EVENTID.

SELECT DISTINCT [EVENT].[EventID], [EVENT].[Name] & ', ' &
[EVENT].[FILENUMBER] AS [Event Name and Filenumber], [EVENT].[Active] FROM
EVENT LEFT JOIN RENTAL ON [EVENT].[EVENTID]=[RENTAL].[EventID] WHERE
((([EVENT].[Active])=-1));

:

You need to look at the Bound Column property of the combobox. The values in
this column needs to be the same data you are referencing in the
ControlSource property. If you are displaying concatenated data but want to
bind to a single value, create a hidden column and bind to it. You can hide a
column by setting it's width to 0 in the ColumnWidths property.

Barry

:

The value from the form's record source isn't showing. I have data in the
dropdown portion.

:

When you say "All data shows except the combo box", do you mean the combobox
doesn't have any data in it's dropdown portion or that the value from the
form's recordsource is not displaying in the combobox?

Barry

:

I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I figured out the problem a while back but I'll post it for those of you who
might want to know or are having a similar issue:

The problem was in the combo box control properties. I forgot that I had
set the combo box to show only data marked active. If I tried to edit a
record that was not marked active I wouldn't get all of the data. So, I
pulled out the Active = Y and things work now.

fsuds said:
C'mon... anyone??? I've been going at this for a month now.

fsuds said:
I've set up some code trying to get the cbo to display a record but it only
displays my SQL statement. How do I get the statement to run and then my cbo
to disply the results? Here is the code:

Dim strSQL As String

strSQL = "Select ([EVENT].[NAME])&', '&([EVENT].[FILENUMBER])" & _
" From EVENT" & _
" WHERE [EVENT].[EVENTID] = forms![frmEditRental].[txtEventID].Value"

Me.cmbEvent = strSQL

fsuds said:
I have a form that I'm working on and it's almost complete except for a
problem I'm encounering with a combo box. The form's record source is a SQL
statement passed through open args from another form. All data shows except
the combo box and I'm assuming that is because it's row source contains
concatenated data.

How can I get this thing to display the correct data on form load? Here is
my code.

frmRentalSearch
Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID]," & _
" ([EVENT].[Name])&', '&([EVENT].[FILENUMBER]) AS EVENT," & _
" RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]," & _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], RENTALITEM.[UNIT]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub

frmEditRental
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
Click to expand...
Click to expand...
Click to expand...
 

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

passing data to new form using open args 5
Open form based on combo box selection 2
Filter Combo Box 4
Combo Box and SubForm 5
Deleting a Form & SubForm Record 1
Need combo box to refresh when moving to new record 2017 1
How to update form fields based on combo-box value change 1
Cascading combo box confusion 10

Top