What's wrong with my code??

[beacon]

I'm using Access 2003 and I'm trying to get a field on one form to output data based on a query that is partly based on the user's selection.

Basically, user chooses option which makes option either 1 or 2. User then clicks cmdSubmit and it loads a different form that iniates a query to populate a field based on whether or not the option is a 1 or a 2.

I have passed the value and have the correct data appearing in the correct field, but it's not sorted. The table, where the data pulls from, is sorted, but for some reason when I use ORDER BY, I keep getting error messages.

Here's the code for cmdSubmit:

Private Sub cmdSubmit_Click()
    Dim UserChoice As Variant
    UserChoice = [optionGroup].Value
    DoCmd.OpenForm "frmEmployee", acNormal, , , acFormAdd, , UserChoice
    DoCmd.Close acForm, Me.Name
End Sub

And here's the code for the form load:

Private Sub Form_Load()
    Dim UserChoice As Variant
    Program.RowSourceType = "Table/Query"
    Program.RowSource = "SELECT * FROM tblProgram WHERE ProgramID = " & Me.OpenArgs
End Sub

I have tried placing ORDER BY tblprogram.prog ram after the FROM statement and also separately after me.openargs. Obviously I'm missing something somewhere.

Any ideas??

[ADezii]

I'm using Access 2003 and I'm trying to get a field on one form to output data based on a query that is partly based on the user's selection.

Basically, user chooses option which makes option either 1 or 2. User then clicks cmdSubmit and it loads a different form that iniates a query to populate a field based on whether or not the option is a 1 or a 2.

I have passed the value and have the correct data appearing in the correct field, but it's not sorted. The table, where the data pulls from, is sorted, but for some reason when I use ORDER BY, I keep getting error messages.

Here's the code for cmdSubmit:

Private Sub cmdSubmit_Click()
    Dim UserChoice As Variant
    UserChoice = [optionGroup].Value
    DoCmd.OpenForm "frmEmployee", acNormal, , , acFormAdd, , UserChoice
    DoCmd.Close acForm, Me.Name
End Sub

And here's the code for the form load:

Private Sub Form_Load()
    Dim UserChoice As Variant
    Program.RowSourceType = "Table/Query"
    Program.RowSource = "SELECT * FROM tblProgram WHERE ProgramID = " & Me.OpenArgs
End Sub

I have tried placing ORDER BY tblprogram.prog ram after the FROM statement and also separately after me.openargs. Obviously I'm missing something somewhere.

Any ideas??

Try:
[CODE=vb]Private Sub Form_Load()
Me![Program].RowSourceType = "Table/Query"
Me![Program].RowSource = "SELECT * FROM tblProgram WHERE ProgramID = " & Me.OpenArgs & " ORDER BY tblProgram.Prog ram;"
End Sub[/CODE]

[beacon]

Try:
[CODE=vb]Private Sub Form_Load()
Me![Program].RowSourceType = "Table/Query"
Me![Program].RowSource = "SELECT * FROM tblProgram WHERE ProgramID = " & Me.OpenArgs & " ORDER BY tblProgram.Prog ram;"
End Sub[/CODE]

That didn't work either. It keeps returning with a syntax error, saying that I'm missing an operator in between where I place ORDER BY and whatever I stick it next to. It's almost like it doesn't recognize ORDER BY.

Adding the Me![Program] didn't change anything, but I appreciate the idea.

[Denburt]

I keep getting error messages.

That sentence from your first post doesn't tell us much, it helps if you provide the err.number and or err.description it really helps us when you provide that info since we could play a guessing game till the end of time and still not find a solution.

In your last post you mentioned the error message stated that you were missing an operator, so I would try testing or checking two things.

In your tblProgram make sure that programID is a number, if it isn't then you need to wrap this in quotes in your query. Example below:

You could also check to make sure that you are providing a number or string to this query in your openArgs statement.

Here are a couple of examples of things to change and hopefully this helps.

If ProgramID is text:
[CODE=VB]Private Sub Form_Load()
Me![Program].RowSourceType = "Table/Query"
Me![Program].RowSource = "SELECT * FROM tblProgram WHERE ProgramID = '" & CStr(Me.OpenArg s) & "' ORDER BY tblProgram.Prog ram;"
End Sub
[/CODE]

If it is a number:
[CODE=VB]Private Sub Form_Load()
Me![Program].RowSourceType = "Table/Query"
Me![Program].RowSource = "SELECT * FROM tblProgram WHERE ProgramID = " & CLng(Me.OpenArg s) & " ORDER BY tblProgram.Prog ram;"
End Sub
[/CODE]


Let us know if this helps.

[beacon]

That sentence from your first post doesn't tell us much, it helps if you provide the err.number and or err.description it really helps us when you provide that info since we could play a guessing game till the end of time and still not find a solution.

In your last post you mentioned the error message stated that you were missing an operator, so I would try testing or checking two things.

In your tblProgram make sure that programID is a number, if it isn't then you need to wrap this in quotes in your query. Example below:

You could also check to make sure that you are providing a number or string to this query in your openArgs statement.

Here are a couple of examples of things to change and hopefully this helps.

If ProgramID is text:
[CODE=VB]Private Sub Form_Load()
Me![Program].RowSourceType = "Table/Query"
Me![Program].RowSource = "SELECT * FROM tblProgram WHERE ProgramID = '" & CStr(Me.OpenArg s) & "' ORDER BY tblProgram.Prog ram;"
End Sub
[/CODE]

If it is a number:
[CODE=VB]Private Sub Form_Load()
Me![Program].RowSourceType = "Table/Query"
Me![Program].RowSource = "SELECT * FROM tblProgram WHERE ProgramID = " & CLng(Me.OpenArg s) & " ORDER BY tblProgram.Prog ram;"
End Sub
[/CODE]


Let us know if this helps.

I'm sorry for leaving the err.number and the err.description out, but I haven't really learned how to use either yet. I'm constantly fooling with Access, Visual Basic, and C++, so anytime someone tells me I've left something out I try really hard to teach myself how to use it.

My error message kept telling me that there was a syntax error and that I was missing an operator. I realized that I didn't have a space between my " and ORDER BY, so the statement thought that the ProgramID = 1 and ORDER BY were all together. Once I fixed that it worked like a charm.

I didn't initially include more info because I had a feeling that I was just overlooking something. I've found that that is generally my problem with programming, either that or I overanalyze my problems.

Anyway, I appreciate all of your help. This is a work project and now that I've gotten this one section figured out all the rest of it will be easy because it basically mimicks what I've already done.

Thanks,
beacon