Recordsource / subform issue

[laszlopmagyar]

Hi guys!

I got a little project and I almost finished but got a little problem in the end.


I have a main form (NAI_form) and a lot of info on that. The users are use to fill out every field

one by one, but after I reviewed the access databases found out I can help them. There are a couple data what's

the same for every state. So I created a second table, primary key the state...


In the main form there is an Add new record button.


In the main Form I have a subform (NAI_subform) , the recordsource set to NAI in the begining. The NAI is the main table.


The main table NAI and the second table has the same fields.


So what I'm trying to do is when they click on add new record the NAI_subform's recordsource should change from NAI to second table.


I have a combo box (Combo_state) what's connected to a query and depend on what's selected from the combo list will filter the second database.


So sub click on add new record and then select something from the list, the subform will be filled with the right info from the second database


At this point if I set the combobox visible in the beginning and select one of the option from the combolist and then click on add record

the sub form will change it's source and show what I need. The problem is as soon as I hide the combo box so the box after click on add new record

the sub form not going to show anything.

I checked the source of the sub form and that's ok. the combo box shows the options but no more connection with sub form anymore


here's the code:

Private Sub Form_Current()
   
    If Me.NewRecord Then
    

 
    Me.NAI_subform.Form.RecordSource = "State_Record"
    
    Me.Combo_state.Visible = True
    
    
    Else
    
    Me.Combo_state.Visible = False
    Me.NAI_subform.Form.RecordSource = "NAI"
    End If

End Sub


    
End Sub
Private Sub Add_New_Record_Click()
On Error GoTo Err_Add_New_Record_Click
 

 DoCmd.GoToRecord , , acNewRec
 
 Me.NAI_subform.Form.RecordSource = "State_Record"
 
     
Exit_Add_New_Record_Click:
    Exit Sub

Err_Add_New_Record_Click:
    MsgBox Err.Description
    Resume Exit_Add_New_Record_Click
    
End Sub

Private Sub Combo_state_AfterUpdate()
   
   If Me.NewRecord Then
    
    Me.NAI_subform.Form.RecordSource = "State_Record"
    Me.NAI_subform.Form.Requery
    
    Else
    
    End If
         
End Sub