Combo box not working

  • Thread starter Thread starter Adrienne
  • Start date Start date
A

Adrienne

Back by popular demand:
I have to use a VBA code on a project.

I have a Form_Copy of qry:CANTEST and a table entitled
tbl:CANFY2003 I'm trying to build a combo box and perform
a Visual Basic Autofill for a project that I'm working on.

I went into the existing text box labeled NEW_CAN and
changed it to a combo box. After which I pulled up the
properties tab and typed into Row Source the following:

Select
NEWCAN#,txtCAN,txtRESEARCH,txtDIVISION,txtPOOL,txtNotes
From tbl:CANFY2003ORDER BY txtNEWCAN#;

Also I typed into column widths:

0";6";0";0";0"

Next, I click on AfterUpdate and open the Code window and
typed:


Option Compare Database

Private Sub Form_AfterUpdate()

End Sub

Private Sub NEW_CAN___AfterUpdate()
' columns are zero based
' control_on_form = combo_box_column ()

NEW_CAN = Me.NEW_CAN__.Column(1) '0789"
Research = Me.NEW_CAN__.Column(2) 'BIOD"
Division = Me.NEW_CAN__.Column(3) 'DAIDS"
POOL = Me.NEW_CAN__.Column(4) 'RPG"
Notes = Me.NEW_CAN_.Column(5) 'Research Projects"

End Sub

I tried to run the code but no error occurred and when I
went back to the form and clicked on Datasheet view the
NEW_CAN# field is now blank???? What am I doing wrong, I
have 150 different CAN #'s and over 6000 transaction. I
have to use a VBA Code to do an auto fill for the columns
above. Maybe you can urgently help!!!
 
Adrienne said:
Back by popular demand:
I have to use a VBA code on a project.

I have a Form_Copy of qry:CANTEST and a table entitled
tbl:CANFY2003 I'm trying to build a combo box and perform
a Visual Basic Autofill for a project that I'm working on.

I went into the existing text box labeled NEW_CAN and
changed it to a combo box. After which I pulled up the
properties tab and typed into Row Source the following:

Select
NEWCAN#,txtCAN,txtRESEARCH,txtDIVISION,txtPOOL,txtNotes
From tbl:CANFY2003ORDER BY txtNEWCAN#;
Click to expand...
You have NEWCAN# and txtNEWCAN#. Maybe it should be

Select txtNEWCAN#,txtCAN,txtRESEARCH,txtDIVISION,txtPOOL,txtNotes
From tbl:CANFY2003ORDER BY txtNEWCAN#;

See your previous thread - about special characters in names. Does the
table name really have a colon in it? A better name would be
tblCANFY2003ORDER.

Also I typed into column widths:

0";6";0";0";0"
Click to expand...
This will show only the txtCan field
Next, I click on AfterUpdate and open the Code window and
typed:


Option Compare Database
Click to expand...

Add Option Explicit

Private Sub Form_AfterUpdate()

End Sub

Private Sub NEW_CAN___AfterUpdate()
' columns are zero based
' control_on_form = combo_box_column ()

NEW_CAN = Me.NEW_CAN__.Column(1) '0789"
Research = Me.NEW_CAN__.Column(2) 'BIOD"
Division = Me.NEW_CAN__.Column(3) 'DAIDS"
POOL = Me.NEW_CAN__.Column(4) 'RPG"
Notes = Me.NEW_CAN_.Column(5) 'Research Projects"

End Sub
Click to expand...

There are too many underscores in the procedure. This changes to sub
name (the control is NEW_CAN, but the code if for a control named
NEW_CAN_ ) and disconnects it from the control. If the control is named
NEW_CAN, then the code should look like:

Private Sub NEW_CAN_AfterUpdate()
' columns are zero based
' control_on_form = combo_box_column ()

NEW_CAN = Me.NEW_CAN.Column(1) '0789"
Research = Me.NEW_CAN.Column(2) 'BIOD"
Division = Me.NEW_CAN.Column(3) 'DAIDS"
POOL = Me.NEW_CAN.Column(4) 'RPG"
Notes = Me.NEW_CAN.Column(5) 'Research Projects"

End Sub


I tried to run the code but no error occurred and when I
Click to expand...

because the code didn't run. See above...
went back to the form and clicked on Datasheet view the
NEW_CAN# field is now blank???? What am I doing wrong, I
Click to expand...

Is the field named NEWCAN# OR txtNEWCAN# ?
have 150 different CAN #'s and over 6000 transaction. I
have to use a VBA Code to do an auto fill for the columns
above. Maybe you can urgently help!!!
Click to expand...


Keep trying, you're getting closer...
 
Back
Top