Option Buttons

  • Thread starter Thread starter htmldiva
  • Start date Start date
H

htmldiva

I created a table of clients with home and office addresses. Some o
them have one or the other. Some of them have both. On my user form
I have radio buttons on my form to select whether the home or office i
the primary address. The value is 1 for home; 2 for office. Thes
values are stored in the table in my primaryaddr field.

I want to create labels to show the either address depending on whic
radio button is selected. I need help constructing the query. An
suggestions
 
As I understand it you want to have a control on the form that displays
the home address if [primaryaddr] = 1 and the office address if
[primaryaddr] = 2.

Start by creating a textbox on the form, large enough to hold the number
of lines in the address. Let's call it txtAddress. Then create a Sub
procedure in the form's module to set its value when required, like this
(which is untested air code):

Private Sub DisplayAddress(AddrType As Long)
Dim dbD As DAO.Database
Dim rsR As DAO.Recordset
Dim strSQL As String

Set dbD = CurrentDB()

'Build the SQL statement to get the address,
'formatted in a single string
Select Case AddrType
Case 1 'home
'Modify the statements clauses below to use your
'actual field names
strSQL = "SELECT HomeAddress & Chr(13) & Chr(10) " _
& "& HomeCity & ", " & HomePostcode AS Address "
Case 2 'office
strSQL = "SELECT OfficeAddress & Chr(13) & Chr(10) " _
& "& OfficeCity & ", " & OfficePostcode AS Address "
End Select
'Modify the next line to use your actual table name
'and primary key
strSQL = strSQL & "FROM MyTable WHERE ID = " _
& Me.ID.Value & ";"

'Get the value
Set rsR = dbD.OpenRecordset(strSQL)
Me.txtAddress.Value = rsR.Fields("Address").Value

'Tidy up
rsR.Close
Set rsR = Nothing
Set dbD = Nothing
End Sub

Then call this function in the form's Current event procedure (so the
primary address will be displayed when the form moves from one record to
another) and in the AfterUpdate event procedure of the option group
containing the radio buttons. The line of code to call the function will
be like this:

DisplayAddress Me.primaryaddr.Value
 
Back
Top