Corrupted database?

[mackmaru]

Access 2007
I have a issue creating combo boxes to find a current record
I use the wizard to create the combo box
- Select records on my form based on the value I selected in my combo box


- Select the Field I want to have in my search
- complete the wizard as required

Then when using the combo box, I make my selection and it just doesn't work. The record does not update to what I've selected. The Event Procedure for AfterUpdate is

Object Type - blank
Object Name - blank
Record - First
Where Condition - ="[Membership ID] = " & Str(Nz(Screen.ActiveControl,0))

When I try to see the VB Code, Access always shuts down and restarts.

Guidance required here please.

[June7]

I don't use the wizards because they do things I don't like.

What VB code? Looks like you are using macro - I only use VBA.

Exactly how are you 'trying to see the VB Code'?

Is [Membership ID] a number data type?

Advise not to use space nor punctuation/special characters (underscore only exception) in naming convention.

If you want to provide db for analysis, follow instructions at bottom of my post.

[mackmaru]

I've added the underscore on the master copy. Here is the zip copy for you. Please check and let me know.

[June7]

Code behind Form_180s:

Membership ID = [Forms]![Form_Membership]![Membership ID]

Note the space in Membership ID. This code will fail. How should VBA understand that Membership and ID are parts of 1 name? That's what [ ] delimiters are for. Or don't use spaces in names. Also, if you open Form_180s to an existing record, this code would replace value in existing record. This code should only run if the form opens to a new record. You changed name of field but not the textbox.

If Me.NewRecord Then Me.Membership_ID = [Forms]![Form_Membership]![Membership_ID]

Have you considered using a form/subform arrangement?

Why do you have Charges field in Table_Membership?

Aside from spaces, you are also using punctuation/special characters in naming convention. Advise not to do that, with the exception of underscore. Report still references the old field name in textbox.

[mackmaru]

Membership ID = [Forms]![Form_Membership]![Membership ID]
I found this issue and fixed it.

Where do I put this? If Me.NewRecord Then Me.Membership_ID = [Forms]![Form_Membership]![Membership_ID]
My combo box still not working.

[June7]

That If Then code is to replace the original line. However, need to use Current event instead of Open.

If you mean the combobox on Client Form - it works for me.

[mackmaru]

i dont think I'm fully understanding this. I cant get it to work.
I managed to get to VB for the ComboBox AfterUpdate
added this line line: If Me.NewRecord Then Me.Membership_ID = [Forms]![Form_Membership]![Membership_ID], Is this right.
Sorry for the hassle mate.

[June7]

The combobox embedded macro was working for me. Did you convert it to VBA?


Instead of

Private Sub Form_Open(Cancel As Integer)
    Membership ID = [Forms]![Form_Membership]![Membership ID]
End Sub

Use

Private Sub Form_Current()
    If Me.NewRecord Then Me.Membership_ID = [Forms]![Form_Membership]![Membership_ID]
End Sub

[mackmaru]

This is what I mean when I say I can't use VBA. Access keeps shutting down. Is it possible for you to send me your copy of my db and I'll see if that works?

[June7]

But I had no problem opening your db and the VBA editor so it can't be db corruption. You cannot open the VBA editor? This an issue with another db?

If you can't use VBA, then what do you mean by "I managed to get to VB for the ComboBox AfterUpdate"?

[CJ_London]

This is what I mean when I say I can't use VBA. Access keeps shutting down

there are a number of ways of opening the vba editor - which one are you using?

[mackmaru]

VBA is working ( It must have been me) I'm opening up from Database Tools toolbar.
Do I put this code in now?
Private Sub Form_Current()
If Me.NewRecord Then Me.Membership_ID = [Forms]![Form_Membership]![Membership_ID]
End Sub

[June7]

That code must be behind form. Another way to open VBA editor is through the form event property on Property Sheet. Select [Event Procedure] then click the ellipsis (…) to open editor at that event code, add the line shown. You must have done this at some time because the form already Load event code.

Or if you have the form's code module open in the editor, copy/paste the suggested code, all 3 lines.

[mackmaru]

That's where it crashes
" Property Sheet. Select [Event Procedure] then click the ellipsis (…) to open editor at that event code"
I'll try this "
Or if you have the form's code module open in the editor, copy/paste the suggested code, all 3 lines."

[mackmaru]

is it possible for you to send me your copy and see if that works?