I'm having trouble figuring out what I need to do here. Basically, I
want to pull data from several different tables and send them to a form
using open args. However, the form that is being fed need to have
certain columns of data concatenated. Here is the code I have so far.
I'm sure it's not right because it doesn't work right.

Private Sub cmdEdit_Click()
Dim sSQL As String

sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID], EVENT.[NAME]" & _
" EVENT.[FILENUMBER], RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]" &
_
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE]," & _
" RENTALITEM.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM " & _
"WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


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

Thanks for any help.

Fred

Try this:

DoCmd.OpenForm "frmEditRental", acNormal
Forms!frmEditRental.RecordSource = sSQL

Hope that helps!



fredindy wrote:

I'm having trouble figuring out what I need to do here. Basically, I
want to pull data from several different tables and send them to a form
using open args. However, the form that is being fed need to have
certain columns of data concatenated. Here is the code I have so far.
I'm sure it's not right because it doesn't work right.
>
Private Sub cmdEdit_Click()
Dim sSQL As String
>
sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID], EVENT.[NAME]" & _
" EVENT.[FILENUMBER], RENTAL.[RENTALDATE], RENTALITEM.[RENTALID]" &
_
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE]," & _
" RENTALITEM.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM " & _
"WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"
>
>
DoCmd.OpenForm "frmEditRental", acNormal, OpenArgs:=sSQL
End Sub
>
Thanks for any help.
>
Fred

The form being opened has Me.RecordSource = Me.OpenArgs in the On Load
event. Would this be the same as what you've stated?

Jeff L wrote:

Try this:
>
DoCmd.OpenForm "frmEditRental", acNormal
Forms!frmEditRental.RecordSource = sSQL
>
Hope that helps!
>
>
>
fredindy wrote:

When I click on cmdEdit here is the error I get.

Run-time error '3075'
Syntax error (missing operator) in query expression 'EVENT.[NAME]
EVENT.[FILENUMBER]'.

fredindy wrote:

The form being opened has Me.RecordSource = Me.OpenArgs in the On Load
event. Would this be the same as what you've stated?
>
Jeff L wrote:

fredindy wrote:

When I click on cmdEdit here is the error I get.
>
Run-time error '3075'
Syntax error (missing operator) in query expression 'EVENT.[NAME]
EVENT.[FILENUMBER]'.
>

before setting anything, make sure your SQL statement works.

build up to the SQL statement (sSQL = ....)

then use Debug.Print to output ot the immediate window. Copy all that,
paste it into a blank query (click the SQL button), and run the query.
If that works, your problem is elsewhere.

You've got a couple of commas (,) missing

Should be something like:-
sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID], EVENT.[NAME], " _
& "EVENT.[FILENUMBER], RENTAL.[RENTALDATE], " _
& "RENTALITEM.[RENTALID], " _
& "RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], " _
& "RENTALITEM.[PRICEPERUNIT], RENTAL.[QUANTITY] " _
& "FROM RENTALITEM " _
& "WHERE RENTAL.[RID] = forms![frmRentalSearch].[RID]"



--

Terry Kreft


"fredindy" <fsudler@yahoo.comwrote in message
news:1155174200.920399.18960@q16g2000cwq.googlegro ups.com...

When I click on cmdEdit here is the error I get.
>
Run-time error '3075'
Syntax error (missing operator) in query expression 'EVENT.[NAME]
EVENT.[FILENUMBER]'.
>
fredindy wrote:

I

form

far.

RENTALITEM.[RENTALID]" &

>

I noticed that this morning and fixed it. Now, it is asking me for the
values of RID and EVENTID. Not sure if there are any past that because
I didn't have an exact event ID to plug in at that moment.
Terry Kreft wrote:

You've got a couple of commas (,) missing
>
Should be something like:-
sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID], EVENT.[NAME], " _
& "EVENT.[FILENUMBER], RENTAL.[RENTALDATE], " _
& "RENTALITEM.[RENTALID], " _
& "RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE], " _
& "RENTALITEM.[PRICEPERUNIT], RENTAL.[QUANTITY] " _
& "FROM RENTALITEM " _
& "WHERE RENTAL.[RID] = forms![frmRentalSearch].[RID]"
>
>
>
--
>
Terry Kreft
>
>
"fredindy" <fsudler@yahoo.comwrote in message
news:1155174200.920399.18960@q16g2000cwq.googlegro ups.com...

I

form

far.

RENTALITEM.[RENTALID]" &

If it's popping a dialog up to ask for those values then check that the
fields actually appear in the tables and that you have spelt the fieldnames
correctly.

--

Terry Kreft


"fredindy" <fsudler@yahoo.comwrote in message
news:1155215830.346906.20160@m79g2000cwm.googlegro ups.com...

I noticed that this morning and fixed it. Now, it is asking me for the
values of RID and EVENTID. Not sure if there are any past that because
I didn't have an exact event ID to plug in at that moment.
Terry Kreft wrote:

Load

Basically,

a

have

so

>

Actually, on the form it's feeding, I have 2 combo boxes that have
concatenated data in them. I'll check and make sure that all the field
names and tables are correct but I wonder that since the data is
concatenated, that might be causing the issue.

Terry Kreft wrote:

If it's popping a dialog up to ask for those values then check that the
fields actually appear in the tables and that you have spelt the fieldnames
correctly.
>
--
>
Terry Kreft
>
>
"fredindy" <fsudler@yahoo.comwrote in message
news:1155215830.346906.20160@m79g2000cwm.googlegro ups.com...

Load

Basically,

a

have

so

Here is the SQL from both forms

frmSearchRental

sSQL: Select RENTAL.[RID], RENTAL.[EVENTID], EVENT.[NAME],
EVENT.[FILENUMBER], RENTAL.[RENTALDATE], RENTALITEM.[RENTALID],
RENTALITEM.[PRICEPERUNIT], RENTAL.[QUANTITY]
FROM RENTALITEM
WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]

frmEditRental

cmbEVENT: SELECT ([RENTAL].[EventID]) & ", " & nz([EVENT].[Name]) & ",
" & nz([EVENT].[FILENUMBER]) AS Expr1
FROM EVENT INNER JOIN RENTAL ON [EVENT].[EVENTID]=[RENTAL].[EventID];

cmbPrice: SELECT [RENTALITEM].[RentalID], ([RENTAL.RENTALITEM]) & ", "
& ([RENTAL].[RENTALTYPE]) & ", " & ([RENTAL].[PRICEPERUNIT]) AS Expr1
FROM RENTAL INNER JOIN RENTALITEM ON
[RENTAL].[RentalID]=[RENTALITEM].[RentalID];

txtRentalDate: RentalDate
txtRID: RID
txtQuantity: Quantity

fredindy wrote:

Actually, on the form it's feeding, I have 2 combo boxes that have
concatenated data in them. I'll check and make sure that all the field
names and tables are correct but I wonder that since the data is
concatenated, that might be causing the issue.
>
Terry Kreft wrote:

I have adjusted my code a little. I noticed that I was missing a
couple of columns so I added them in. No more errors when I click the
cmdEdit button however, I do not have all of my data on the new form.
Could this be from concatenating data in the combo box source and not
in sSQL? Below is my updated code. Thanks for all the help so far.


sSQL = "Select RENTAL.[RID], RENTAL.[EVENTID], EVENT.[NAME]," & _
" EVENT.[FILENUMBER], RENTAL.[RENTALDATE], RENTALITEM.[RENTALID],"
& _
" RENTAL.[RENTALITEM], RENTAL.[RENTALTYPE]," & _
" RENTAL.[PRICEPERUNIT], RENTAL.[QUANTITY]" & _
" FROM RENTALITEM, RENTAL, EVENT" & _
" WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]"


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


fredindy wrote:

Here is the SQL from both forms
>
frmSearchRental
>
sSQL: Select RENTAL.[RID], RENTAL.[EVENTID], EVENT.[NAME],
EVENT.[FILENUMBER], RENTAL.[RENTALDATE], RENTALITEM.[RENTALID],
RENTALITEM.[PRICEPERUNIT], RENTAL.[QUANTITY]
FROM RENTALITEM
WHERE forms![frmRentalSearch].[RID] = RENTAL.[RID]
>
frmEditRental
>
cmbEVENT: SELECT ([RENTAL].[EventID]) & ", " & nz([EVENT].[Name]) & ",
" & nz([EVENT].[FILENUMBER]) AS Expr1
FROM EVENT INNER JOIN RENTAL ON [EVENT].[EVENTID]=[RENTAL].[EventID];
>
cmbPrice: SELECT [RENTALITEM].[RentalID], ([RENTAL.RENTALITEM]) & ", "
& ([RENTAL].[RENTALTYPE]) & ", " & ([RENTAL].[PRICEPERUNIT]) AS Expr1
FROM RENTAL INNER JOIN RENTALITEM ON
[RENTAL].[RentalID]=[RENTALITEM].[RentalID];
>
txtRentalDate: RentalDate
txtRID: RID
txtQuantity: Quantity
>
fredindy wrote:

fredindy wrote:

I have adjusted my code a little. I noticed that I was missing a
couple of columns so I added them in. No more errors when I click the
cmdEdit button however, I do not have all of my data on the new form.
Could this be from concatenating data in the combo box source and not
in sSQL? Below is my updated code. Thanks for all the help so far.

If the ComboBoxes are bound to one of the fields, then you need to NOT
have the concatinated values as the Bound Column. The bound control
needs to have the bound column be the field in the table or query. If
you do not want that column to display then set its width to 0 (zero).

--
Bri

The bound column is the UID and is hidden. For instance an employee
named Joe S. Blow has the EmployeeID of 29 it SHOULD show, Blow, Joe,
S. in the cmb. Blow, Joe, S. being the data concatenated in column 2
and 29 hidden in column 1.

I also have a form setup similar to this one that is strictly for
entering new data. Using the same code, I have all the data available
to me to select. However, when I save it, the record in the table
doesn't have all of the selected data. I'm absolutley lost here.

Fred
Bri wrote:

fredindy wrote:

>
If the ComboBoxes are bound to one of the fields, then you need to NOT
have the concatinated values as the Bound Column. The bound control
needs to have the bound column be the field in the table or query. If
you do not want that column to display then set its width to 0 (zero).
>
--
Bri

Sounds like you are doing the combo's right. Not sure what you are
doing wrong though. Sorry.

Bri

fredindy wrote:

The bound column is the UID and is hidden. For instance an employee
named Joe S. Blow has the EmployeeID of 29 it SHOULD show, Blow, Joe,
S. in the cmb. Blow, Joe, S. being the data concatenated in column 2
and 29 hidden in column 1.
>
I also have a form setup similar to this one that is strictly for
entering new data. Using the same code, I have all the data available
to me to select. However, when I save it, the record in the table
doesn't have all of the selected data. I'm absolutley lost here.
>
Fred

Anyone have any ideas?
Bri wrote:

Sounds like you are doing the combo's right. Not sure what you are
doing wrong though. Sorry.
>
Bri
>
fredindy wrote:

Still in need of help.
fredindy wrote:

Anyone have any ideas?
Bri wrote: