Please help with combo boxes and VBA coding, thanks. 1

[vamoose]

I need some assistance with MSAccess 2000 VBA please. I have a form with 2 combo boxes on it.
The first combo box looks up a value from a table using: Select tbl_labels.label from tbl_labels;
The second combo box uses another table and:
Select tbl_paper.paper from tbl_paper;
I would now like to copy the selected value from combo box #2, tbl_paper, into tbl_labels to the value of combo box #1, tbl_labels.
I hope this makes sense. Thank you very much.

 

[jrbarnett]

Use AfterUpdate event of tbl_Paper, it will look something like:

Private Sub tbl_Paper_AfterUpdate()
  
  tbl_Labels.Value = tbl_Paper.Value

End Sub

John

 

[vamoose]

Thank you for the code, I do believe it will work. I have installed it with combobox #2, tbl_paper, after update and the error "Object required", "Run-time error '424':" now appears. Any suggestions ?

Thanks again.

 

[vamoose]

I am trying this:

Private Sub Paper_AfterUpdate()
tbl_labels.paper.value = paper.value
End Sub

and I get the error "Object required" Can anyone see what I might be missing here? Thanks.

 

[vamoose]

Also trying this:

Private Sub Paper_AfterUpdate()

DoCmd.RunSQL "Update set tbl_labels.paper.value = " & Paper.Value & " where tbl_labels.label.value = " & Label.Value
MsgBox "New association successfully created"
DoCmd.Close

End Sub

Now I get the error "Syntax error in UPDATE statement.

 

[jrbarnett]

Hi Vamoose,

In your first statement
tbl_labels.paper.value = paper.value

the reason you get an "Object Required" is that Access cannot find an object (control) called tbl_labels or paper - need to know more to find out which.

In your second attempt, there is a syntax error in your SQL update statement.

The general form of an SQL update statement is:

UPDATE table SET field=value WHERE condition

You have omitted the table name from this list. Put it in and it should work fine.
----
My version sets the value of the control, rather than editing the underlying table. I try to avoid doing this, as it allows users to see the results of any manipulations on screen prior to storing any changes.

I'd also look very carefully at the naming of your controls as I think it very confusing.
In the Reddick VBA naming convention (that I am using), the "tbl" prefix is used for tables. Controls on forms that utilise those tables should be named in accordance with the control type - cbo for a Combobox, txt for a textbox etc.
For more information about the Reddick VBA naming conventions, see

http://www.xoc.net/standards/default.asp.

I should say that there is no requirement to follow them, however they are very widely used by Access and VB developers worldwide, and doing so would make your code far easier for other people to understand.

John