By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 890 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

sql and passing through open args

P: n/a
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

Aug 9 '06 #1
Share this Question
Share on Google+
15 Replies


P: n/a
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
Aug 9 '06 #2

P: n/a
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:
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
Aug 10 '06 #3

P: n/a
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:
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
Aug 10 '06 #4

P: n/a

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.

Aug 10 '06 #5

P: n/a
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" <fs*****@yahoo.comwrote in message
news:11*********************@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:
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:
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

Aug 10 '06 #6

P: n/a
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" <fs*****@yahoo.comwrote in message
news:11*********************@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:
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:
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
Aug 10 '06 #7

P: n/a
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" <fs*****@yahoo.comwrote in message
news:11*********************@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:
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" <fs*****@yahoo.comwrote in message
news:11*********************@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:
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:
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
>

Aug 10 '06 #8

P: n/a
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" <fs*****@yahoo.comwrote in message
news:11*********************@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:
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" <fs*****@yahoo.comwrote in message
news:11*********************@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:
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:
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
Aug 10 '06 #9

P: n/a
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:
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" <fs*****@yahoo.comwrote in message
news:11*********************@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:
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" <fs*****@yahoo.comwrote in message
news:11*********************@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:
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:
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
>
>
Aug 10 '06 #10

P: n/a
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:
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" <fs*****@yahoo.comwrote in message
news:11*********************@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:
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" <fs*****@yahoo.comwrote in message
news:11*********************@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:
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:
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
Aug 10 '06 #11

P: n/a
Bri

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

Aug 14 '06 #12

P: n/a
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:
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
Aug 15 '06 #13

P: n/a
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
Aug 16 '06 #14

P: n/a
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:
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
Aug 17 '06 #15

P: n/a
Still in need of help.
fredindy wrote:
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:
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
Aug 24 '06 #16

This discussion thread is closed

Replies have been disabled for this discussion.