Target a specific number

[onefootout]

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

 

[GingerR]

So, trying to sort this out. You have a table of addresses, but there are only five records and there will only ever be five records? How did you come up with the five-digit number to cause something to "display" or not? These 5-digit numbers are stored in a table as well? What else is in the table?

Ok, essentially, can you please detail your table structure and the overall gist of the process? Thanks.

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

http://r937.com/relational.html

 

[onefootout]

Ok, so maybe that was too much information.

So basically, what I want to do is use controls on a form to change each specific number in a 5 digit string. The string resides in a table with other employer info.

This string is used by a query to display addresses in a form, but that's kind of irrelevant.

Can I do this using an update query, and if so, how do I target each number individually?

 

[GingerR]

Well there are a variety of things you could do depending on how you have the form set up. I suppose if you have five Yes/No boxes, each one represents digit 1-5, right?

So either as a person checks/unchecks each one, you could right then update the data in the table. OR if they check/uncheck them as they wish then hit a button, and in the button's OnClick event you can update the data.

So name the checkboxes chk1, chk2, etc. where each number represents a certain digit. I have a field name of "DisplayAddress" which holds the 5-digit code, and a record ID of "ID". My table is named "Table1". I have a button that when clicked, has this code:

    Dim strSingleDigit, strDigits As String
    Dim strChkName As String
    
    Dim i As Integer
    
    strDigits = ""
    strSingleDigit = ""
    
    
    'Loop thru check boxes and build string
    For i = 1 To 5
        strChkName = "chk" & i
        For Each Control In Me.Controls
            If Control.Name = strChkName Then
                If Control.Value = True Then
                    strSingleDigit = "2"
                Else
                    strSingleDigit = "1"
                End If
            End If
        Next Control
        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

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

http://r937.com/relational.html

 

[onefootout]

I can see how this could work, but honestly, it's a little beyond me.

Is there an answer that might be simpler for me to understand and implement, or should I admit defeat?

Thanks for your help GingerR!

 

[GingerR]

This is pretty simple, so try to keep moving forward.

For testing this, make a copy of your database (or at least the table and form you are dealing with here). On your form put 5 check boxes.

Name them chk1, chk2, chk3, chk4, chk5.

Put a button on the form. If the wizard comes up, cancel it. Copy the code above. If the PROPERTIES window is not already visible, right-click on the button and pick PROPERTIES. In the OnClick property of the button, click in the OnClick event row where it says [Event Procedure] and click the BUILD BUTTON (the little button to the right with three dots on it). Inside there, paste the code between the SUB and END SUB statements.

Replace the word "TABLE1" with the name of your table.
Replace the word "DisplayAddress" with the name of your combo box.
Replace the word "ID" with the name of your ID field.

Go back to the form, save it and close it. Re-open it. Check/uncheck the check boxes and each time you want the 5-digit code to change and watch it change.

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

http://r937.com/relational.html

 

[onefootout]

Ok, I tried this, and it doesn't like "control" in the loop that checks the check boxes. It spits out a compile error: variable not defined.

'Loop thru check boxes and build string
For i = 1 To 5
strChkName = "chk" & i
For Each control In Me.Controls <---Here, on the first instance of "control".

What am I missing here?

Thanks again for the help!

 

[GingerR]

Odd, cause it's not a variable. It's an Object. Are you copying the code directly from your module to the last post, or is it the code I wrote? In your module, the word "control" should be capitalized (Access should have done this itself, not you capitalizing it cause it's an MS Access Object).

Somewhere in your code module, on a new line, type

me.

and a pop-up list should come up. Is CONTROLS in there?

again, on a new line, type

for each control in me.controls (lower case)

and hit return and it should change all first letters to capitals. Does it do that?

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

http://r937.com/relational.html

 

[onefootout]

That's what why I was confused also.

Yep, controls is in the popup.

The only thing it capitalizes is the M in 'me' when I type that entire line in.

I also tried capitalizing control each time it appears, but that didn't change anything.

 

[GingerR]

Maybe it has to do with References, I'm not sure. I can't imagine it, but we'll see. In your code module, go to the top menu and pick TOOLS+REFERENCES. I have these, and i don't think the version matters:


Visual Basic for Applications
Microsoft Access 11.0 Object Library
Microsoft ActiveX Data Objects 2.5 Library
Microsoft DAO 3.6 Object Library

Again, I can't imagine this being the issue but check and make sure you have these things checked. Again,I think version wouldn't matter.

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

http://r937.com/relational.html

 

[onefootout]

Well, there's a references selection underneath tools, but it's grayed out and I can't click on it. Any other place I might be able to find that information?

 

[GingerR]

Is your code in the middle of running (something highlighted)? If so, STOP the code (use the stop-looing button or RUN+RESET from the menu) then pick TOOLS+REFERENCES.

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

http://r937.com/relational.html

 

[onefootout]

Man, I feel like a nub.

I've got VB for apps, Access 8.0 Object Library, and DAO 3.51 Object Library.

 

[GingerR]

Why a nub?

Really, I don't know the issue. Using "CONTROLS" is not rocket science. I'm gonna see if anyone can help out, so hang on.

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

http://r937.com/relational.html

 

[onefootout]

Thanks a lot GingerR,

I feel like I should be able to figure this out, and it's frustrating that I'm doing such a bad job.

 

[GingerR]

Don't sweat it, we've all been there. We'll get there, don't worry about it. It'll be cool when it's done

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

http://r937.com/relational.html

 

[onefootout]

Thanks

 

[GingerR]

So you have Access 97? The Access 8.0 Object Library should have the Controls object in it. This is selected by default in your database, it's not something you should have to even worry about.

If you want to, try sending me your db, at datachick10 at yahoo dot com and i'll take a look. if it's big, just make a new db with the tables/forms we are dealing with.

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

http://r937.com/relational.html

 

[GingerR]

just checking - you put this code in the OnClick event of the button?

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

http://r937.com/relational.html

 

[onefootout]

Yup, it's in the onClick event.

I'll put together something to send over.

Thanks again!