Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations MikeeOK on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Target a specific number

Status
Not open for further replies.

onefootout

Technical User
Oct 23, 2007
42
US
I'm really hoping this makes sense to someone.

I have a combo box in a form. It displays an address based on number. The number has five digits, each representing one address. If you want the address to display in the box, the corresponding number is 2, if not, the number is 1.

So, for instance, the atlanta address is the first digit, and you want it to display. The number would be 21111.

If you wanted boston, the 2nd digit, to be displayed as well, the number would be 22111.

Currently, to change this number, you have to do it manually in the table.

What I'm trying to do is have some check boxes so the user can change this themselves.

I'll have some yes/no controls on a form, and a button to run an update query. I was thinking something like this, but I don't know how to target one number at a time. I'm not really even sure you can use an update query for something like this.

UPDATE employer

SET Address[somehow target second digit] = '2'

WHERE [theform]![checkbox]=1
 
Try it like this.

Dim strSingleDigit, strDigits As String
Dim strChkName As String

Dim i As Integer
Dim c as Control

strDigits = ""
strSingleDigit = ""


'Loop thru check boxes and build string
For i = 1 To 5
strChkName = "chk" & i
For Each c In Me.Controls
If c.Properties("Name") = strChkName Then
If c = True Then
strSingleDigit = "2"
Else
strSingleDigit = "1"
End If
End If
Next c
strDigits = strDigits & strSingleDigit
Next i

'Get the record in the table and edit it
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SElect * from Table1 where ID = " & Me.ID)

rs.Edit
rs!DisplayAddress = strDigits
rs.Update

Set rs = Nothing

'Refresh the current form record to show the new string
Me.DisplayAddress.Requery
 
Hey there--just as RivetHed says....it's funny (I've never seen this) but at the top I did

Dim ctrl as Control

And I didn't even alter the rest of the code, the keyword "Control" then capitalized itself and it ran fine.

Other issues: You need to name the check boxes "chk1", "chk2", etc.

Also the code wasn't in the button's Click event, but maybe that's because you cludged together something to send me.

Also you have to change "address" to "assignmentcolor".

Also the field "assignment color" has to be on the form.

Also the field "assignmentcolor" has to be text (for my code to run). If it's really a "Number", you'd have to change the code to make it a number instead of a string.

SO....all of that being said, I have some other more basic questions/suggestions:

Where does this 5-digit number come from? I wonder if, even if this code works, it'd be better a different way. I mean, so the user sees 5 check boxes and none of them are checked. Then they see this 5-digit number like 21121. They want to change it. Seems it'd be more helpful if the check boxes are checked or not to reflect the current 5-digit number. Then, I was thinking, why not just have the 5 check boxes be in the table, and the 5-digit number just be a thing that is calculated on-the-fly (with a simpler function that we already have) and simply displayed in queries, forms and reports and users need it? Then what they would see is the 5 cities and check boxes, checked or not depending on whatever it is they are doing; then this little 5-digit number is just a calculation that is displayed when they/you need it. That means tho that there will always ever be 5 cities, or that if there are more added you'd have to add check boxes and tweak the code. Anyhow, maybe I'm way off base, but I'm just wondering if there's a better way to do all of this?

Ginger

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at
 
Hey guys,

Thank you so much for your help with this! I really appreciate the time and effort you took to help me out!

I was finally told that it's way too complicated, and I have the go-ahead to just stick the number on a form for people to manually change. I'm still going to work on it, to see if there's a better answer. If I get it, great. If not, no big deal.

Thanks again, you guys are great!
Onefootout

 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top