Link Combo Box

M

Mickster

Registered User.

Local time

Yesterday, 17:12

Joined

Feb 10, 2005

Messages

38

• May 12, 2006

• #1

Hi there, I am new to access and this question be a no brainer for you access gurus. What I currently have is a form with one combo box and two radio buttons.

Staff and Status

The staff combo box is a drop down menu of all staff members and is derived from a table called Employees. The two radio buttons represent "Active" and "Inactive".

So if John Smith is selected their current status will be shown on the radio button and the user can change it if they want to.

The problem I am having is linking the radio button to a name in the combo box. Any assistance would be appreciated.

Thanks

 

clerics

Registered User.

Local time

Today, 09:12

Joined

Nov 28, 2005

Messages

82

• May 15, 2006

• #2

in tbls Employees , Make sure that you'd added status colunm already !
status will be true or false ( mean "Active" or "Inactive") .

Let see :
Form.RecordSource = "Employees"
and
Combo1.RowSource = "SELECT [Employees].[ID] FROM Employees;

then write this code for Combo1's Event:

Code:

Private Sub combo1_AfterUpdate()
    
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = '" & Me![combo1] & "'"
    Me.Bookmark = rs.Bookmark
End Sub

ok now you can do what you want !

 

M

Mickster

Registered User.

Local time

Yesterday, 17:12

Joined

Feb 10, 2005

Messages

38

• May 15, 2006

• #3

clerics said:

in tbls Employees , Make sure that you'd added status colunm already !
status will be true or false ( mean "Active" or "Inactive") .

Let see :
Form.RecordSource = "Employees"
and
Combo1.RowSource = "SELECT [Employees].[ID] FROM Employees;

then write this code for Combo1's Event:

Code:

Private Sub combo1_AfterUpdate()
    
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = '" & Me![combo1] & "'"
    Me.Bookmark = rs.Bookmark
End Sub

ok now you can do what you want !

Click to expand...

What does is stored in the Bookmark variable?

 

clerics

Registered User.

Local time

Today, 09:12

Joined

Nov 28, 2005

Messages

82

• May 15, 2006

• #4

all of records in Employees (table) where ID of Staff = ID (be chose by users)
will be stored in Bookmark

Set rs = Me.Recordset.Clone <--- get record from Form's recordsource to rs
rs.FindFirst "[ID] = '" & Me![combo1] & "'" <---if ID(in record) = ID(in Form)
Me.Bookmark = rs.Bookmark <--- Set Form 's recordsource again with this ID

Just try to do it ! you can know how it work ! Goodluck friend

 

M

Mickster

Registered User.

Local time

Yesterday, 17:12

Joined

Feb 10, 2005

Messages

38

• May 15, 2006

• #5

Hi, Thanks for your help. I have placed your code as you specified. But it still doesnt seem to work. In the rs.FindFirst"[ID] = ...... line of the code shouldnt ID be replaced with the employeeName field since I am displaying the name in the combo box?

Where do I add code in the option group so that the value is passed onto the Status field. I have Acitve set as 1 and Inactive as 2. How will this translate into true or false?

Thanks

 

clerics

Registered User.

Local time

Today, 09:12

Joined

Nov 28, 2005

Messages

82

• May 16, 2006

• #6

here is example !

 

Attachments

• ratio.zip

  17.1 KB · Views: 387

M

Mickster

Registered User.

Local time

Yesterday, 17:12

Joined

Feb 10, 2005

Messages

38

• May 16, 2006

• #7

clerics said:

here is example !

Click to expand...

Hey Clerics, I am currently running Access 97. Is there any way that you can convert it.

Thanks for the help