Problems with subforms and vba updates (1 Viewer)

Grumm · Nov 27, 2015

[SOLVED] Problems with subforms and vba updates

Hello,

I have a problem with subforms and updating the parent recordsource with VBA.
I have 1 table with columns Article,DIM1,DIM2,FROM,TO,Discount
(I probably will get a lot of comments to make 2 tables... If i can't avoid it then i will change.)
So the "FROM" "TO" allows the user to enter multiple discounts.
Example :
Article1;color1;size1;0;50;3.5%
Article1;color1;size;50;100;3.5%
Article2;color1;size2;0;50;2.5%

Now i want to create the input field dynamic. So I already made a form where i select all the records with a given article,dim1 and dim2. Allowed the user to add new records or delete records in that table.

Code:

"select * from discountTable where article='" & Me.Article.value & "' and DIM1='" & Me.DIM1 & "' and DIM2='" & Me.DIM2 & "'"

Now I try to make a sub-form where the user can select an article. This sub form has a query who group the articles from this table.

Code:

"select Article,DIM1,DIM2 from discountTable group by Article,DIM1,DIM2"

When they select an other article from the sub-form i want to update the parent form to display the correct records. (I do this now by populating 3 fields on the main form and then run a sub on that form.)

I managed already to do this. But sometimes, the sub-form is stuck in the update vba code. And i can't select an other article.

How can i achieve this without problems ?
(If something isn't clear, let me know. I can make a small database to show what i already have if needed)

Thanks,
Grumm

CJ_London · Nov 27, 2015

FROM and TO are reserved words, I strongly recommend you change them to something more meaningful. Using reserved words in table and field/control names can generate unexpected and ambiguous errors so this may be your issue.

Otherwise, since you say it is the vba code which sticks, it would be helpful for you to post the code - all of it, not just the line you think it sticks on

Grumm · Nov 27, 2015

I understand.
In the sub-form, i have this code behind the article field :

Code:

Private Sub Article_ID_GotFocus()
    Me.DIM_1.SetFocus
    Form_ContainerPrijzen_Main.Tekst15.value = Me.Article_ID
    Form_ContainerPrijzen_Main.Tekst17.value = Me.DIM_1
    Form_ContainerPrijzen_Main.Tekst19.value = Me.DIM_2

    Form_ContainerPrijzen_Main.Tekst15_AfterUpdate
End Sub

Here you can see the view : 2015-11-27_131333.png
The sub-form is a dataview and the code is triggered when the user click an 'article_id' cell. Has you can see in the code, i needed to move the focus to an other cell before updating the main form. If i don't do that, the focus in the sub-form crash and I can't select anything on that form.

Then in the main form i have this :

Code:

Public Sub Tekst15_AfterUpdate()
    Dim s As String
    Dim sWhere As String
    
    s = "SELECT ContainerPrijzen.START, ContainerPrijzen.FINISH, ContainerPrijzen.Netto FROM ContainerPrijzen"
    sWhere = " WHERE ((ContainerPrijzen.Article_ID) = '" & Me.Tekst15.value & "')"
    If Not IsNull(Me.Tekst17.value) Then
        sWhere = sWhere & " And ((ContainerPrijzen.DIM_1) = '" & Me.Tekst17.value & "')"
    End If
    If Not IsNull(Me.Tekst19.value) Then
        sWhere = sWhere & " And ((ContainerPrijzen.DIM_2) = '" & Me.Tekst19.value & "')"
    End If
    Me.RecordSource = s & sWhere
End Sub

This will just update the recordsource and display the correct fields. It is a normal header/detail/footer view.

CJ_London · Nov 27, 2015

END is also a reserved word

suggest use Parent. rather than Form_ContainerPrijzen_Main.

This

Code:

If IsNull(Me.Tekst17.value) Then
    Else
        sWhere = sWhere & " And ((ContainerPrijzen.DIM_1) = '" & Me.Tekst17.value & "')"
End If

should be written as

Code:

If not IsNull(Me.Tekst17.value) Then
        sWhere = sWhere & " And ((ContainerPrijzen.DIM_1) = '" & Me.Tekst17.value & "')"
End If

You also don't need .value - it is the default

But sometimes, the sub-form is stuck in the update vba code

can you clarify what you mean by this - what line does it stick on - do you get an error? If so, what?

Grumm · Nov 27, 2015

What happens is that as soon as I enter the sub-form again, the Article_ID_GotFocus is triggered.

No error. Just unable to select a different line. With the code i provided now it works. But the problem still happens if you select the row instead of the actual cell.
About the if's i've already changed that one. (maybe not fast enough so you could not see it

)

About the .value, i like adding it, that way i know it is the value. Even if it is useless.
I didn't use Parent because i'm still a noob in access coding. When i don't see options coming up with "ctrl+space", I thing i'm doing something wrong.

Here is a little video to show the behavior of it :
Video Here

Grumm · Nov 30, 2015

I will try a list menu. That way I don't need a sub-form and hopefully the focus issue will be fixed.

My fault to over complicate the problem

Problems with subforms and vba updates (1 Viewer)

Grumm

Registered User.

CJ_London

Super Moderator

Grumm

Registered User.

CJ_London

Super Moderator

Grumm

Registered User.

Grumm

Registered User.

Similar threads

Users who are viewing this thread