VBA verbage on unbound combo box in form

[cp1981]

Whats up everybody. Just want to see whats wrong with the VBA lingo that I have to open a form using an unbound combo box.

No matter what I select it takes me to my "transaction" form

Private Sub cboNewForm_AfterUpdate()
Dim strForm As String

If Me.cboNewForm = "Account" Then
  strForm = "frmAccount"
ElseIf Me.cboNewForm = "Category" Then
  strForm = "frmCategory"
Else
  strForm = "frmTransaction"
End If

DoCmd.OpenForm strForm

End Sub

Also, how do I ensure it will open up a new record and not got to the first one?

[Bob Fitz]

What is the Row Source proprty setting of "cboNewForm" and what is the Bound Column setting.

[June7]

Probably the value of combobox is an ID, not the descriptive text the code is expecting. Have you step debugged? Review link at bottom of my post for debugging guidelines. Follow the code as it executes, what value is shown for cboNewForm? As Bob suggested, provide the SQL statement of the combobox RowSource.

Options for opening form:

1. another line in your code:
DoCmd.GoToRecord , , acNewRec

2. Open form in Add Record mode, existing records will not be available
DoCmd.OpenForm strForm, , , , acFormAdd

[ssanfu]

Maybe add a message box to see what data is returned....

Private Sub cboNewForm_AfterUpdate()
Dim strForm As String

msgbox Me.cboNewForm

If Me.cboNewForm = "Account" Then
  strForm = "frmAccount"
ElseIf Me.cboNewForm = "Category" Then
  strForm = "frmCategory"
Else
  strForm = "frmTransaction"
End If

DoCmd.OpenForm strForm

End Sub

[cp1981]

Guys,

Ive since had to do some re-tweaking of the database. Im about to setup the combo box and re-attempt the VBA. Regardless, Ill let yall know what happens.

BTW, I have two tables: tblCategory, tblAccount. One account can have many categories, and obviously one category can have many accounts.

ex. Account: Wal Mart, Categories: Food, Tobacco, Home Improvement, etc. or Category: Gas, Account: Gas Station A, Gas Station B.....

When selecting an account from my Account combo box, how can I just select "Wal Mart" and not have 500 Wal Mart's to choose from?

[cp1981]

What is the Row Source proprty setting of "cboNewForm" and what is the Bound Column setting.

SELECT tblFormString.ID, tblFormString.Form
FROM tblFormString
ORDER BY tblFormString.Form;

Bound Column: 1

[June7]

The combobox value is the ID. The code is looking for text ("Account", "Category") which will never match the combobox. Either change the combobox to bind to column 2 (could maybe remove the ID field from the SQL) or change the code to look for ID or change the code to reference column 2 by its index - index begins with 0.

Me.cboNewForm.Column(1)

Is the combobox bound? Are you saving the ID into a record?

[cp1981]

Well, right now my db is locked. A msg pops up says the "user Admin has locked my machine to prevent changes"...to the effect. Its my computer and its not on any network. Wth...anyways, the combobox is unbound and as far as the ID, I guess I don't understand. As far as the tblFormString is concerned no I'm not saving anything to it. Just a table to set up a list of forms...hopefully that answers your question.

Just a lil frustrated cuz I can't access my db.

[June7]

The value of the combobox is the ID. Your code expects the value to be "Account" or "Category". The code will never match the combobox value and therefore it always hits "frmTransaction". What is not clear about that and the suggested options to correct?

[ssanfu]

Also, be aware that "Form" is a reserved word in Access:

SELECT tblFormString.ID, tblFormString.Form
FROM tblFormString
ORDER BY tblFormString.Form;

Maybe "FormName" would be a better field name.

Here is a list of reserved words:
http://www.allenbrowne.com/AppIssueBadWord.html

[cp1981]

The value of the combobox is the ID. Your code expects the value to be "Account" or "Category". The code will never match the combobox value and therefore it always hits "frmTransaction". What is not clear about that and the suggested options to correct?

I understood about what you said about bounding the right column or deleting the ID field. I was just making sure I understood what u were saying about saving the ID. Like i said, i was a bit frustrated that ny db was locked by some ghost user named Admin.

So when I get my db squared away or re-do it, ill update u. Thanks for the help. Much appreciated.