linking combo box to subform for additions

[Jaymin]

hope someone can help, I have a form with three cascading combo boxes and a subform, the third combo provides a bound field that joins it to the subform, i use this code on the "on change" event on the third combo box,

Private Sub cboPPE_Change()
Dim strFilter As String
strFilter = "IDPPE = " & Me.cboPPE
Forms!frmMain!subformMaint.Form.filter = strFilter
Forms!frmMain!subformMaint.Form.FilterOn = True
End Sub

I want the subform to be for entry of data only but when i select the data field from the combo box, the subform selects the filtered data for the combo box, can you advise how to change the above code so that the subform stays to additons only.
Peter

 

[bob fitz]

Have you set the sub forms "Data Entry" property to Yes.

 

[cplmckenzie]

Jaymin,

Can you post the form, subform and underlying code?

cplmckenzie

 

[Jaymin]

Thanks guys for your help
My scenario is that I have a form named “frmMain” on it has three cascading combo boxes, the first named “cboStation”, the second “cboStaff” and the third is “cboPPE” and a sub form called subfrmMaint to enter the required data
Both forms have the “data entry” and “allow additions” set to yes.
What happens at the moment is when the last combo box has been selected it sets the subform so that you can view the list of entered records pertaining to the selection of the third combo box.
What I want to happen is when I have selected the item from the last combo box it is to position it in the first tab index of the subform and have the subform set to addition only, I do not need to view any records only add them.
This is the form coding .
Option Compare Database

Private Sub cboPPE_Change()
Dim strFilter As String

strFilter = "IDPPE = " & Me.cboPPE

Forms!frmMain!subformMaint.Form.filter = strFilter
Forms!frmMain!subformMaint.Form.FilterOn = True
End Sub

Private Sub cboStaff_AfterUpdate()
Me.cboPPE = Null
Me.cboPPE.Requery

End Sub

Private Sub cboStation_AfterUpdate()

Me.cboStaff = Null
Me.cboPPE = Null


Me.cboStaff.Requery
Me.cboPPE.Requery
End Sub

tblStation
pk IDStation
stationName

tblStaff
pk IDStaff
firstName
surname
Fk IDStation

tblPPE
Pk IDPPE
chipNumber
fk IDStaff
fk IDMaint

tblMaintenance
pk IDMaint
Date
afterUse
repairs
fk IDPPE

 

[bob fitz]

[COLOR=#222222]Private Sub cboPPE_Change()[/COLOR]
[COLOR=#222222][FONT=Verdana]Dim strFilter As String[/FONT][/COLOR]
 
[COLOR=#222222][FONT=Verdana]strFilter = "IDPPE = " & Me.cboPPE[/FONT][/COLOR]
 
[COLOR=#222222][FONT=Verdana]Forms!frmMain!subformMaint.Form.filter = strFilter[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Forms!frmMain!subformMaint.Form.FilterOn = True[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]

Why do you need to set a filter?
Try this:

[COLOR=#222222]Private Sub cboPPE_Change()[/COLOR]
[COLOR=#222222][FONT=Verdana]Dim strFilter As String[/FONT][/COLOR]
 
[COLOR=#222222][FONT=Verdana]strFilter = "IDPPE = " & Me.cboPPE[/FONT][/COLOR]
 
[COLOR=#222222][FONT=Verdana]Forms!frmMain!subformMaint.Form.filter = strFilter[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Forms!frmMain!subformMaint.Form.FilterOn = True[/FONT][/COLOR]
[FONT=Verdana][COLOR=red][B]Me.[COLOR=red]subformMaint.Form.DataEntry = True[/COLOR][/B][/COLOR][/FONT]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]

 

[Jaymin]

Bob Fitz,
Thanks for you reply, that part seems to work but now i get an error message when i try to save the data. I think it may be in my relationship.
i have included the error message and the relationship table, and if need be the data base.
Hope you can help.
many thanks
Peter

 

[bob fitz]

I have made some changes to your db (Attached).
I have removed this line:
Me.subformMaint.Form.DataEntry = True
I think the main problem was that the Main form and the Sub form were not linked. When you tried to save a new record in the sub form, it needs a value for the foreign key field, which is of course the PK of the Main table. Take a look at the subform controls properties. You will see that I have entered a value for the Link Master Fields and the Link Child Fields.

However, if I have misunderstood your requirements and this is not working as you wanted, please post back.

 

[Jaymin]

Bob Fitz,
I would like to thank you very much for you help and time in helping me with my problems.
All is working well now
Peter

 

[bob fitz]

Glad to help.

A couple of other things that I’ve just noticed.

1) In your table “tblMaintenance”, you have a field called “Date”. This is a poor choice for the field name. “Date” is a “reserved” word in Access. There is a built-in function called “Date()”. I would strongly recommend that you change this. Leaving it as it is will almost certainly cause you a problem sooner or later. In any case, it’s not very descriptive of the data held. I notice that its description in the table is “date of washing”, so calling it “WashDate” would be much better IMHO.

2) I can’t see any need for the field “IDmaint” in table “tblPPE”.

3) I would turn off “Name AutoCorrect”. I believe there are many that consider this to be a cause of corruption (during development) in many databases.

 

[Jaymin]

Bob Fitz

3) I would turn off “Name AutoCorrect”. I believe there are many that consider this to be a cause of corruption (during development) in many databases.

where do you find this, is it in the "options", and thanks for the other
suggestions
Peter

 

[bob fitz]

Peter,

In the version that I use (A2003) it can be found in "Options", on the "General" tab.

 

[Jaymin]

In the version that I use (A2003) it can be found in "Options", on the "General" tab.

I have the same version thanks Bob fitz for all your help, have a great day.....