Form Combo Box

S

soak 'n fused

Access is fairly new to me, so my question may sound fairly ignorant and not
very well worded. Anyone's help will be greatly appreciated.

I would like to display all of the table data in all of the form fields after
the selection from oneof the Form fields that is a Combo Box labeled Site
Name. How do you do this? Does that make any sense?
 
K

Klatuu

Here is the customary way to do this. This code will go in the After Update
event of your combo box. Of course, the names will have to change to suit
your form and data.

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
'Find the Activity Selected
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") = vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtDESCRIPTION.SetFocus
End If
Else
Me.Bookmark = rst.Bookmark
Me.txtDESCRIPTION.SetFocus
End If
rst.Close
Set rst = Nothing
Me.cboActivity = Null

End Sub
 
S

soak 'n fused

Klatuu,

Thank you for your help. Unfortunately I'm dumber than you might think. :)
I'm not familiar with Visual Basic. Do I need to delete the old code, paste
the code you provided and change the names? BC#, Address, and Scope are a
few of the field names. Where do I place those within the code?

Darrel

Here is the customary way to do this. This code will go in the After Update
event of your combo box. Of course, the names will have to change to suit
your form and data.

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
'Find the Activity Selected
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") = vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtDESCRIPTION.SetFocus
End If
Else
Me.Bookmark = rst.Bookmark
Me.txtDESCRIPTION.SetFocus
End If
rst.Close
Set rst = Nothing
Me.cboActivity = Null

End Sub
Access is fairly new to me, so my question may sound fairly ignorant and not
very well worded. Anyone's help will be greatly appreciated.

I would like to display all of the table data in all of the form fields after
the selection from oneof the Form fields that is a Combo Box labeled Site
Name. How do you do this? Does that make any sense?
Click to expand...
Click to expand...
 
K

Klatuu

Not dumb, probably, just inexperienced.
Here is what you will need to change:

Me.cboActivity - Me. + the name of your combo box

This line puts the current value of the combo box into a text box that is
the bound control. txtActivity would be the control bound to the field you
are doing the look up on.
Me.txtActivity = Me.cboActivity

This line just sets the focus (makes it the currently selected) to the text
box named txtDESCRIPTION

Me.txtDESCRIPTION.SetFocus

You will only be looking up one field name, to you need not worry about the
others.
Replace the code you have in your After Update event for the combo box with
what I posted and change the names above to suit you form.

soak 'n fused said:
Klatuu,

Thank you for your help. Unfortunately I'm dumber than you might think. :)
I'm not familiar with Visual Basic. Do I need to delete the old code, paste
the code you provided and change the names? BC#, Address, and Scope are a
few of the field names. Where do I place those within the code?

Darrel

Here is the customary way to do this. This code will go in the After Update
event of your combo box. Of course, the names will have to change to suit
your form and data.

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
'Find the Activity Selected
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") = vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtDESCRIPTION.SetFocus
End If
Else
Me.Bookmark = rst.Bookmark
Me.txtDESCRIPTION.SetFocus
End If
rst.Close
Set rst = Nothing
Me.cboActivity = Null

End Sub
Access is fairly new to me, so my question may sound fairly ignorant and not
very well worded. Anyone's help will be greatly appreciated.

I would like to display all of the table data in all of the form fields after
the selection from oneof the Form fields that is a Combo Box labeled Site
Name. How do you do this? Does that make any sense?
Click to expand...
Click to expand...
Click to expand...
 
S

soak 'n fused

Klatuu,

so when I'm done, this part:
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _

should look like this?
If MsgBox("Add Activity Number " & Me.Site_Name & " To the
Attribute Table", _

and this:
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtDESCRIPTION.SetFocus
End If
Else
Me.Bookmark = rst.Bookmark
Me.txtDESCRIPTION.SetFocus

like this?
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.Site_Name.SetFocus
End If
Else
Me.Bookmark = rst.Bookmark
Me.Site_Name.SetFocus


Here is the customary way to do this. This code will go in the After Update
event of your combo box. Of course, the names will have to change to suit
your form and data.

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
'Find the Activity Selected
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") = vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtDESCRIPTION.SetFocus
End If
Else
Me.Bookmark = rst.Bookmark
Me.txtDESCRIPTION.SetFocus
End If
rst.Close
Set rst = Nothing
Me.cboActivity = Null

End Sub
Access is fairly new to me, so my question may sound fairly ignorant and not
very well worded. Anyone's help will be greatly appreciated.

I would like to display all of the table data in all of the form fields after
the selection from oneof the Form fields that is a Combo Box labeled Site
Name. How do you do this? Does that make any sense?
Click to expand...
Click to expand...
 
K

Klatuu

See notes below

soak 'n fused said:
Klatuu,

so when I'm done, this part:
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _

should look like this?
Click to expand...
You might want to change the text inside the quotes to what is meaningful
for your table. like "Add Site Name " and " To the Your Table Name"
If MsgBox("Add Activity Number " & Me.Site_Name & " To the
Attribute Table", _

and this:
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtDESCRIPTION.SetFocus
End If
Else
Me.Bookmark = rst.Bookmark
Me.txtDESCRIPTION.SetFocus

like this?
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Click to expand...
Change txtActivity to the name of your text box and change cboActivity to
the name of your combo box.
Me.txtActivity = Me.cboActivity
Me.Site_Name.SetFocus
End If
Else
Me.Bookmark = rst.Bookmark
Me.Site_Name.SetFocus


Here is the customary way to do this. This code will go in the After Update
event of your combo box. Of course, the names will have to change to suit
your form and data.

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
'Find the Activity Selected
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") = vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtDESCRIPTION.SetFocus
End If
Else
Me.Bookmark = rst.Bookmark
Me.txtDESCRIPTION.SetFocus
End If
rst.Close
Set rst = Nothing
Me.cboActivity = Null

End Sub
Access is fairly new to me, so my question may sound fairly ignorant and not
very well worded. Anyone's help will be greatly appreciated.

I would like to display all of the table data in all of the form fields after
the selection from oneof the Form fields that is a Combo Box labeled Site
Name. How do you do this? Does that make any sense?
Click to expand...
Click to expand...
Click to expand...
 
Top