SQL Help

  • Thread starter Thread starter mrrherrera
  • Start date Start date
M

mrrherrera

On my form I have a combo box. That when an item is selected it will bring
up another form. Of the 13 fields available only 5 or will need the separate
forms. I have created a nested if then statement. However, each time I
select one of the items that will bring up the separate form, I get an error
else if without if.
This is the code:

Private Sub Categories_Change()
If Item = "Computer" Then
DoCmd.OpenForm "Computer"
Else
If ([Item] = "Furniture") Then
DoCmd.OpenForm "Furniture"
Else
If ([Item] = "Phones") Then
DoCmd.OpenForm "Narcotics"
Else
If ([Item] = "Vehicle") Then
DoCmd.OpenForm "Vehicle"
Else
[Item] = "Printer"
DoCmd.OpenForm "Printer"
End If
End If
End If
End If



End Sub
 
May be a bit easier to code with a Select Case statement, and it should
probably be in the After Update event;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub
 
Thanks, I no longer get the error, but the other forms don't open. I tried
placinig it in After Update. When that didn't work, I tried it under On
Click. I did change the Private Sub accordingly. Any other suggestions?
This has been kicking me since last week.

Beetle said:
May be a bit easier to code with a Select Case statement, and it should
probably be in the After Update event;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub


--
_________

Sean Bailey


mrrherrera said:
On my form I have a combo box. That when an item is selected it will bring
up another form. Of the 13 fields available only 5 or will need the separate
forms. I have created a nested if then statement. However, each time I
select one of the items that will bring up the separate form, I get an error
else if without if.
This is the code:

Private Sub Categories_Change()
If Item = "Computer" Then
DoCmd.OpenForm "Computer"
Else
If ([Item] = "Furniture") Then
DoCmd.OpenForm "Furniture"
Else
If ([Item] = "Phones") Then
DoCmd.OpenForm "Narcotics"
Else
If ([Item] = "Vehicle") Then
DoCmd.OpenForm "Vehicle"
Else
[Item] = "Printer"
DoCmd.OpenForm "Printer"
End If
End If
End If
End If



End Sub
Click to expand...
Click to expand...
 
Does your combo box have more than one column? If so, is the column
that holds the form names the bound column?
--
_________

Sean Bailey


mrrherrera said:
Thanks, I no longer get the error, but the other forms don't open. I tried
placinig it in After Update. When that didn't work, I tried it under On
Click. I did change the Private Sub accordingly. Any other suggestions?
This has been kicking me since last week.

Beetle said:
May be a bit easier to code with a Select Case statement, and it should
probably be in the After Update event;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub


--
_________

Sean Bailey


mrrherrera said:
On my form I have a combo box. That when an item is selected it will bring
up another form. Of the 13 fields available only 5 or will need the separate
forms. I have created a nested if then statement. However, each time I
select one of the items that will bring up the separate form, I get an error
else if without if.
This is the code:

Private Sub Categories_Change()
If Item = "Computer" Then
DoCmd.OpenForm "Computer"
Else
If ([Item] = "Furniture") Then
DoCmd.OpenForm "Furniture"
Else
If ([Item] = "Phones") Then
DoCmd.OpenForm "Narcotics"
Else
If ([Item] = "Vehicle") Then
DoCmd.OpenForm "Vehicle"
Else
[Item] = "Printer"
DoCmd.OpenForm "Printer"
End If
End If
End If
End If



End Sub
Click to expand...
Click to expand...
Click to expand...
 
It only has one column. Yes, the column is bound.

Beetle said:
Does your combo box have more than one column? If so, is the column
that holds the form names the bound column?
--
_________

Sean Bailey


mrrherrera said:
Thanks, I no longer get the error, but the other forms don't open. I tried
placinig it in After Update. When that didn't work, I tried it under On
Click. I did change the Private Sub accordingly. Any other suggestions?
This has been kicking me since last week.

Beetle said:
May be a bit easier to code with a Select Case statement, and it should
probably be in the After Update event;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub


--
_________

Sean Bailey


:

On my form I have a combo box. That when an item is selected it will bring
up another form. Of the 13 fields available only 5 or will need the separate
forms. I have created a nested if then statement. However, each time I
select one of the items that will bring up the separate form, I get an error
else if without if.
This is the code:

Private Sub Categories_Change()
If Item = "Computer" Then
DoCmd.OpenForm "Computer"
Else
If ([Item] = "Furniture") Then
DoCmd.OpenForm "Furniture"
Else
If ([Item] = "Phones") Then
DoCmd.OpenForm "Narcotics"
Else
If ([Item] = "Vehicle") Then
DoCmd.OpenForm "Vehicle"
Else
[Item] = "Printer"
DoCmd.OpenForm "Printer"
End If
End If
End If
End If



End Sub
Click to expand...
Click to expand...
Click to expand...
 
Hmmmmm. I'm not sure why it wouldn't work. I set up a test db with a form
that has one combo box (the combo box is named Categories) and five
other forms with the appropriate names. It works as expected. Here is
the exact code form my test app;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub

Does other code in your app. work? You may want to compile your code and
do a compact and repair (make a backup first). If you are using A2007 make
sure that your source file is trusted. Other than that, I'm not sure what to
suggest.
--
_________

Sean Bailey


mrrherrera said:
It only has one column. Yes, the column is bound.

Beetle said:
Does your combo box have more than one column? If so, is the column
that holds the form names the bound column?
--
_________

Sean Bailey


mrrherrera said:
Thanks, I no longer get the error, but the other forms don't open. I tried
placinig it in After Update. When that didn't work, I tried it under On
Click. I did change the Private Sub accordingly. Any other suggestions?
This has been kicking me since last week.

:

May be a bit easier to code with a Select Case statement, and it should
probably be in the After Update event;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub


--
_________

Sean Bailey


:

On my form I have a combo box. That when an item is selected it will bring
up another form. Of the 13 fields available only 5 or will need the separate
forms. I have created a nested if then statement. However, each time I
select one of the items that will bring up the separate form, I get an error
else if without if.
This is the code:

Private Sub Categories_Change()
If Item = "Computer" Then
DoCmd.OpenForm "Computer"
Else
If ([Item] = "Furniture") Then
DoCmd.OpenForm "Furniture"
Else
If ([Item] = "Phones") Then
DoCmd.OpenForm "Narcotics"
Else
If ([Item] = "Vehicle") Then
DoCmd.OpenForm "Vehicle"
Else
[Item] = "Printer"
DoCmd.OpenForm "Printer"
End If
End If
End If
End If



End Sub
Click to expand...
Click to expand...
Click to expand...
 
The other code works. I am fairly new to VBS and SQL. I don't have a clue
about compiling the code. I pasted your code and tried it and it still
doesn't work. I just can't figure out what the heck is wrong.

Beetle said:
Hmmmmm. I'm not sure why it wouldn't work. I set up a test db with a form
that has one combo box (the combo box is named Categories) and five
other forms with the appropriate names. It works as expected. Here is
the exact code form my test app;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub

Does other code in your app. work? You may want to compile your code and
do a compact and repair (make a backup first). If you are using A2007 make
sure that your source file is trusted. Other than that, I'm not sure what to
suggest.
--
_________

Sean Bailey


mrrherrera said:
It only has one column. Yes, the column is bound.

Beetle said:
Does your combo box have more than one column? If so, is the column
that holds the form names the bound column?
--
_________

Sean Bailey


:

Thanks, I no longer get the error, but the other forms don't open. I tried
placinig it in After Update. When that didn't work, I tried it under On
Click. I did change the Private Sub accordingly. Any other suggestions?
This has been kicking me since last week.

:

May be a bit easier to code with a Select Case statement, and it should
probably be in the After Update event;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub


--
_________

Sean Bailey


:

On my form I have a combo box. That when an item is selected it will bring
up another form. Of the 13 fields available only 5 or will need the separate
forms. I have created a nested if then statement. However, each time I
select one of the items that will bring up the separate form, I get an error
else if without if.
This is the code:

Private Sub Categories_Change()
If Item = "Computer" Then
DoCmd.OpenForm "Computer"
Else
If ([Item] = "Furniture") Then
DoCmd.OpenForm "Furniture"
Else
If ([Item] = "Phones") Then
DoCmd.OpenForm "Narcotics"
Else
If ([Item] = "Vehicle") Then
DoCmd.OpenForm "Vehicle"
Else
[Item] = "Printer"
DoCmd.OpenForm "Printer"
End If
End If
End If
End If



End Sub
Click to expand...
Click to expand...
Click to expand...
 
From the code window you go to Debug/Compile. I don't know if it will
solve your current problem, but it's always a good idea to make sure your
code compile anyway. Another thing you might do is double check to make
sure that the spelling of the values in your combo box matches the spelling
of the form names and the spelling of the references in the code.
--
_________

Sean Bailey


mrrherrera said:
The other code works. I am fairly new to VBS and SQL. I don't have a clue
about compiling the code. I pasted your code and tried it and it still
doesn't work. I just can't figure out what the heck is wrong.

Beetle said:
Hmmmmm. I'm not sure why it wouldn't work. I set up a test db with a form
that has one combo box (the combo box is named Categories) and five
other forms with the appropriate names. It works as expected. Here is
the exact code form my test app;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub

Does other code in your app. work? You may want to compile your code and
do a compact and repair (make a backup first). If you are using A2007 make
sure that your source file is trusted. Other than that, I'm not sure what to
suggest.
--
_________

Sean Bailey


mrrherrera said:
It only has one column. Yes, the column is bound.

:

Does your combo box have more than one column? If so, is the column
that holds the form names the bound column?
--
_________

Sean Bailey


:

Thanks, I no longer get the error, but the other forms don't open. I tried
placinig it in After Update. When that didn't work, I tried it under On
Click. I did change the Private Sub accordingly. Any other suggestions?
This has been kicking me since last week.

:

May be a bit easier to code with a Select Case statement, and it should
probably be in the After Update event;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub


--
_________

Sean Bailey


:

On my form I have a combo box. That when an item is selected it will bring
up another form. Of the 13 fields available only 5 or will need the separate
forms. I have created a nested if then statement. However, each time I
select one of the items that will bring up the separate form, I get an error
else if without if.
This is the code:

Private Sub Categories_Change()
If Item = "Computer" Then
DoCmd.OpenForm "Computer"
Else
If ([Item] = "Furniture") Then
DoCmd.OpenForm "Furniture"
Else
If ([Item] = "Phones") Then
DoCmd.OpenForm "Narcotics"
Else
If ([Item] = "Vehicle") Then
DoCmd.OpenForm "Vehicle"
Else
[Item] = "Printer"
DoCmd.OpenForm "Printer"
End If
End If
End If
End If



End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thank you for all your help. I will try these tips and see if they work.

Beetle said:
From the code window you go to Debug/Compile. I don't know if it will
solve your current problem, but it's always a good idea to make sure your
code compile anyway. Another thing you might do is double check to make
sure that the spelling of the values in your combo box matches the spelling
of the form names and the spelling of the references in the code.
--
_________

Sean Bailey


mrrherrera said:
The other code works. I am fairly new to VBS and SQL. I don't have a clue
about compiling the code. I pasted your code and tried it and it still
doesn't work. I just can't figure out what the heck is wrong.

Beetle said:
Hmmmmm. I'm not sure why it wouldn't work. I set up a test db with a form
that has one combo box (the combo box is named Categories) and five
other forms with the appropriate names. It works as expected. Here is
the exact code form my test app;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub

Does other code in your app. work? You may want to compile your code and
do a compact and repair (make a backup first). If you are using A2007 make
sure that your source file is trusted. Other than that, I'm not sure what to
suggest.
--
_________

Sean Bailey


:

It only has one column. Yes, the column is bound.

:

Does your combo box have more than one column? If so, is the column
that holds the form names the bound column?
--
_________

Sean Bailey


:

Thanks, I no longer get the error, but the other forms don't open. I tried
placinig it in After Update. When that didn't work, I tried it under On
Click. I did change the Private Sub accordingly. Any other suggestions?
This has been kicking me since last week.

:

May be a bit easier to code with a Select Case statement, and it should
probably be in the After Update event;

Private Sub Categories_AfterUpdate()

Select Case Me.Categories
Case "Computer"
DoCmd.OpenForm "Computer"
Case "Furniture"
DoCmd.OpenForm "Furniture"
Case "Phones"
DoCmd.OpenForm "Narcotics"
Case "Vehicle"
DoCmd.OpenForm "Vehicle"
Case "Printer"
DoCmd.OpenForm "Printer"
End Select

End Sub


--
_________

Sean Bailey


:

On my form I have a combo box. That when an item is selected it will bring
up another form. Of the 13 fields available only 5 or will need the separate
forms. I have created a nested if then statement. However, each time I
select one of the items that will bring up the separate form, I get an error
else if without if.
This is the code:

Private Sub Categories_Change()
If Item = "Computer" Then
DoCmd.OpenForm "Computer"
Else
If ([Item] = "Furniture") Then
DoCmd.OpenForm "Furniture"
Else
If ([Item] = "Phones") Then
DoCmd.OpenForm "Narcotics"
Else
If ([Item] = "Vehicle") Then
DoCmd.OpenForm "Vehicle"
Else
[Item] = "Printer"
DoCmd.OpenForm "Printer"
End If
End If
End If
End If



End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top