SQL Help

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
 
B

Beetle

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
 
M

mrrherrera

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...
 
B

Beetle

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...
 
M

mrrherrera

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...
 
B

Beetle

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...
 
M

mrrherrera

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...
 
B

Beetle

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...
 
M

mrrherrera

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Export to PDF 0
Making a macro only work on certain files 2
VBA Coding Help for Beginner 0
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
Working outlook via vba code with xlsx - error 0
Closing PDF file from Excel VBA (Adobe Reader, Adobe Acrobat, Browser) 0
Dynamic Naming of Form Button 3
Clearing combo boxes and a text box 2

Top