If Then Statement for a Subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to figure out how I can program an If Statement into a subform.
I have already set up a second combo box that is populated after the user
makes a choice from the first combo box. Now what I'm trying to figure out
is how I can setup the statement so that the second combo box selection would
run a query through a series of "if" conditions. I am dealing with number
ranges in account number format (for example: 101.10000, 101.10050, and
210.12575). What I would like to do is by choosing a certain book listing(1A,
1B, or even 6O), the subform with run an SQL statement after testing the "if"
statement. The idea I have followes( I have a little Java programming
experience, but I new to VB):

If (Combo55 = "1A") Then accountInfoSubForm = "SELECT [>101.10000 &
<102.10000] FROM accountInfo ORDER BY accountNumber"
Else If (Combo55 = "1B") Then accountInfoForm = "SELECT [>102.10000 &
<103.10000] FROM accountInfo ORDER BY accountNumber"

And so on til the condition hits a listing of (Combo55 = 6O).
Combo55 is contained within the form printCenter,
accountInfoSubForm is the subform within printCenter,
and the table is listed as accountInfo.
(Sorry to explain, just want to make as clear as possible)

Any help would be greatly appreciated, and I'm everyone's getting sick of my
postings.

FigDaddy
 
Hi, this is not an answer to your question, as such, but a suggestion on a
better way to do this.
Some details are missing from your post so I've improvised a little.

Store the range criteria in a table against the accountNumber [making a new
table if it doesn't exist - think relational]

new table: accountNumbers
accountNumber (text) lowerBound (text) upperBound (text)
1A 101.10000 102.10000
1B 102.10000 103.10000
....
60 ... ...

Set properties of Combo55
RowSource: SELECT accountNumber, lowerBound, upperBound FROM accountNumbers
Order By accountNumber
ColumnCount: 3
ColumnWidths: 3cm,0cm,0cm

Trap the Combo55 AfterUpdate event.

Rather than having an If Then Else, ElseIf statement [which, from memory,
can't contain more than 10 clauses] replace with

' I've rewritten the SQL clause so it makes sense.
accountInfoSubForm.Form.RecordSource = "SELECT * " & _
"FROM accountInfo " & _
"WHERE accountNumber >= '" & Combo55.Column(2) & "' AND
accountNumber <'" & Combo55.Column(3) & "'"

HTH, Graeme.
 
Hey, I tried your suggestion, but I did run into a problem with it. I
modified the accountInfo table, and included the upperBound and lowerBound
fields. From there I set the row source and properties for the Combo55 box. I
created the code in the AfterUpdate in VB. But I didn't caught the "trap the
Combo55 AfterUpdate event". I'm sorry, but I'm not sure what you meant by
that. This is my first major Access project that I've worked on since
college. I apologize if I'm sounding like an idiot, but I don't get the
"trap". Could you possibly help me a touch more. I understand everything
except that. Thank you.

FigDaddy
 
To trap an event is simply to create an event for it.

From the Properties dialog for the Combo55 field
1) select the Event tab
2) click in the After Update property
3) click the button that appears to the right of the property [and select
Event Procedure from dialog if displayed]

The event procedure will be displayed. Enter the code here.

Get this far and come back.
Graeme.

FigDaddy said:
Hey, I tried your suggestion, but I did run into a problem with it. I
modified the accountInfo table, and included the upperBound and lowerBound
fields. From there I set the row source and properties for the Combo55
box. I
created the code in the AfterUpdate in VB. But I didn't caught the "trap
the
Combo55 AfterUpdate event". I'm sorry, but I'm not sure what you meant by
that. This is my first major Access project that I've worked on since
college. I apologize if I'm sounding like an idiot, but I don't get the
"trap". Could you possibly help me a touch more. I understand everything
except that. Thank you.

FigDaddy

Graeme Richardson said:
Hi, this is not an answer to your question, as such, but a suggestion on
a
better way to do this.
Some details are missing from your post so I've improvised a little.

Store the range criteria in a table against the accountNumber [making a
new
table if it doesn't exist - think relational]

new table: accountNumbers
accountNumber (text) lowerBound (text) upperBound (text)
1A 101.10000 102.10000
1B 102.10000 103.10000
....
60 ...
...

Set properties of Combo55
RowSource: SELECT accountNumber, lowerBound, upperBound FROM
accountNumbers
Order By accountNumber
ColumnCount: 3
ColumnWidths: 3cm,0cm,0cm

Trap the Combo55 AfterUpdate event.

Rather than having an If Then Else, ElseIf statement [which, from memory,
can't contain more than 10 clauses] replace with

' I've rewritten the SQL clause so it makes sense.
accountInfoSubForm.Form.RecordSource = "SELECT * " & _
"FROM accountInfo " & _
"WHERE accountNumber >= '" & Combo55.Column(2) & "' AND
accountNumber <'" & Combo55.Column(3) & "'"

HTH, Graeme.
Click to expand...
Click to expand...
 
Back
Top