Data not refreshing

[ElvisR]

I am a novice to VB. That being said:

Some VB scripts in my forms do not refresh the data as I switch records. For example:

This script is links an autonumber field with another. It works fine. However, when I click next record, it just stays to the same data:

          Dim strID As String
          strID = Me.[STATE] & Me.[ID]
          Me.[IDD] = strID

Then I have this script that changes the case of text, but it works when it wants.

    Me.[LAST_NAME] = UCase(Me.[LAST_NAME])
    Me.[FIRST_NAME] = UCase(Me.[FIRST_NAME])
    Me.[MIDDLE] = UCase(Me.[MIDDLE])
    Me.[COMPANY] = UCase(Me.[COMPANY])
    Me.[ADDRESS1] = UCase(Me.[ADDRESS1])
    Me.[CITY] = UCase(Me.[CITY])
    Me.[STATE] = UCase(Me.[STATE])
    Me.[ZIP] = UCase(Me.[ZIP])
    Me.[EMAIL ADDRESS] = UCase(Me.[EMAIL ADDRESS])
    Me.[SPECIALTY] = UCase(Me.[SPECIALTY])
    Me.[REMARKS] = UCase(Me.[REMARKS])

Where do I put them to may them refresh data?

Thanks.

ElvisR

 

[Psycholicon]

You could put both of those groups of code into the OnChange event of the control for your Primary Key, which I'm guessing is the autonumber field ID.

What exactly do you mean "but it works when it wants."?

 

[rhett7660]

You could put a new button on the form using the wizard to save the info. Which would in a sort requery the info. You could also make the "next record" button and have it requery the info on click etc...

Also what I do for the Case part is I put a single line of script in the afterupdate event for each box I want the update to take place in. So when the curser moves off that box it updates. Make sense?

 

[ElvisR]

Thanks Psyh.

What I meant was that at one time, the Ucase worked, but it stopped somehow. I just attached it to the Show All Records button and it worked.

Here is what I am trying to do with the first code: I have two fields (ID and STATE) that I want to merge into one field. I am not sure the best way to get er done, so I decided to try to attach to a string - as seen in my original post. I can then take that data and put it in a unbound textbox on the form.

I would like to know how to get that done inside Access VB or even Access.

Thanks for the help.

 

[ElvisR]

You could put a new button on the form using the wizard to save the info. Which would in a sort requery the info. You could also make the "next record" button and have it requery the info on click etc...

I have those buttons already, but I an not sure how to have it do a requery. Can you show me via a example?

....put a single line of script in the afterupdate event for each box I want the update to take place in. So when the curser moves off that box it updates. Make sense?

Hmmm that sounds like a good idea. Let me try that.

Rhett, please take a look at my last post and see what I am trying to accomplish. I may be going about it the wrong way.

Thanks.

 

[rhett7660]

If am I reading correctly you want this:

your text box to show this:
1 = Ca
2 = Al
3 = Ny

Yes?

 

[rhett7660]

for the requery it is the following:

Depending on when you want it to occur:


Forms!yourformname.Requery

If you are trying to requery say a txt box:

Forms!yourformname.yourtxtcomboboxnamehere.Requery

 

[ElvisR]

If am I reading correctly you want this:

your text box to show this:
1 = Ca
2 = Al
3 = Ny

Yes?

No. I want it to look like this FL003. FL is taken from the STATE field and 003 is taken from the autonumber ID field.

Rhett: I am gonna try the requery. Thanks.

 

[boblarson]

Just an FYI for you on this -

If you want to do something when the user changes from record to record, you use the On CURRENT event. That will capture every time a recordset moves between records and if something changes and the record updates but doesn't move.

 

[ElvisR]

Just an FYI for you on this -

If you want to do something when the user changes from record to record, you use the On CURRENT event.

Works like a charm. Thanks. You guys are really teaching this newbie some good stuff.

I just bought a book, but it is VB6 oriented, not VB access, but the structure is pretty much the same.

 

[boblarson]

I just bought a book, but it is VB6 oriented, not VB access, but the structure is pretty much the same.

Another FYI for you - VBA (Visual Basic for Applications) since Office 2000 has been based on VB6, so that is why they are so similar. Prior versions of Office had their own "macro language."

A good benefit to them being so similar is that once you know one, it is very easy to work in the other; although there are differences which must be taken into account.

 

[ElvisR]

If you want to do something when the user changes from record to record, you use the On CURRENT event.

Can you show me how you would that, since there is no OnCurrent in the Property Sheet. I am trying to have the code below refresh everytime I switch records.

Dim strID As String
strID = Me.[STATE] & Me.[ID]
Me.[IDD] = strID

Me.[IDD] is a textbox.

Also, I have a textbox that is bound to a remarks field on the table. I would like it to create a new line (hard return) when I hit enter. Instead of moving to a next object on the form.

 

[boblarson]

 

[ElvisR]

How do you say WooHoo in VBA? Do.Cmd WooHoo

Thanks alot guys. It is only now I regret the fact that I never followed up programming.

Now I just have one thing with the On Current: When it encounters a blank record - like when I click Add New - it returns the runtime error. Which is due to the null field. I am guessing I need some kind of if an IF statement to correct that. Guidance needed. This is what I came up with, not sure if the statement syntax is great:

    Dim strID As String
    
    If Me.[STATE].Value = "" & Me.[ID].Value = "" Then
        Me.STATE.Value = "No" & Me.[ID].Value = " ID"
    Else
        strID = Me.[STATE] & Me.[ID]
        Me.IDD = strID
    End If

 

[boblarson]

Try changing it to:

    Dim strID As String
[color=red]    If Not IsNull(Me.State) Then[/color]
       If Me.[STATE].Value = "" & Me.[ID].Value = "" Then
           Me.STATE.Value = "No" & Me.[ID].Value = " ID"
       Else
           strID = Me.[STATE] & Me.[ID]
           Me.IDD = strID
       End If
   [color=red] End If[/color]

 

[ElvisR]

Thanks Bob, and all others. Problem resolved.