Saving changes to records on Form

[AdmiralN]

sorry there is no search text on the one I posted before.
I change the recordsource of the subform when there is change in combo and search textbox.

Thanks again. This is exactly what I wanted to implement

 

[AdmiralN]

sorry there is no search text on the one I posted before.
I change the recordsource of the subform when there is change in combo and search textbox.

Hi arnelgp,

Sorry to keep bugging you. Hopefully this is a quick one to solve.

I wanted to keep it so that no records were displayed when the form first opened, but have the option to display all records with a package number 'All' that could be selected from the combopackage.

I thought this could be done by changing the LinkMasterFields to "" when 'ALL' was selected as a package.

My code is below

Private Sub ComboPackage_AfterUpdate()
    If Me.ComboPackage.ListIndex = -1 Or Me.ComboPackage = 0 Then
        Me.LabelActivities.Caption = "Activities"
        If Me.ComboPackage = 0 Then
            ShowPackageFields True
            Me.subFormPackageActivity.Form.RecordSource = "select * from " & strRecordSource & " where (1=0);"
        Else
            Me.subFormPackageActivity.Form.RecordSource = strRecordSource
        End If
    ElseIf Me.ComboPackage.Column(1) = "All" Then
            With Me.subFormPackageActivity
            .LinkMasterFields = ""
            End With
    Else
        
        Me.LabelActivities.Caption = "Activities for " & Me.ComboPackage.Column(1) & " - " & _
        Replace(Me.ComboPackage.Column(2), "&", "&&")
        ShowPackageFields False
        Me.subFormPackageActivity.Form.RecordSource = "select * from " & strRecordSource & " where PackageID=" & Me.ComboPackage
        
    End If
    Me.txtSearchActivity = Null
End Sub

This is causing a runtime error in the following line

Me.subFormPackageActivity.Form.RecordSource = "select * from " & strRecordSource & " where PackageID=" & Me.ComboPackage

 

[arnelgp]

see the rowsource of the combobox.
see also changes in the form code.

 

[AdmiralN]

Works perfectly. I can't thank you enough for all the help you've given!

 

[AdmiralN]

see the rowsource of the combobox.
see also changes in the form code.

Hi Arnel,

I wonder if I can trouble you for your help once more?

My DB no longer saves new records to the correct package number selected in the package combo box. New records are not assigned a package by default.

I not sure what I've changed to cause this.

Thanks.

 

[Gasman]

Arnel has not been around for a while.?

I *think* you wiped out the Master and Child link fields on the subform control.
This now appears to relink them.

HTH

 

[AdmiralN]

Arnel has not been around for a while.?

I *think* you wiped out the Master and Child link fields on the subform control.
This now appears to relink them.

HTH

Thanks so much for your help!

 

[AdmiralN]

Arnel has not been around for a while.?

I *think* you wiped out the Master and Child link fields on the subform control.
This now appears to relink them.

HTH

It seems while solving the bigger problem of saving records with the correct package number, the DB has lost the ability to filter by 'All' records.
This option when selected in the combo box does not show any records.

Any ideas why this may be?

 

[Gasman]

Well I am about to go to work.

It seems arnelgp was not using the link fields, but setting the recordsource in code in the afterupdate event of the combo. So remove those two entries.

I can see a problem with that already as All still allows you to add a new record, when you cannot possibly know which ID it is for.?

So I would look at setting the PackageID from the combo ONLY on new record, and disable Additions if combo is 0.5

Best I can do at present, sorry.

 

[AdmiralN]

Well I am about to go to work.

It seems arnelgp was not using the link fields, but setting the recordsource in code in the afterupdate event of the combo. So remove those two entries.

I can see a problem with that already as All still allows you to add a new record, when you cannot possibly know which ID it is for.?

So I would look at setting the PackageID from the combo ONLY on new record, and disable Additions if combo is 0.5

Best I can do at present, sorry.

Thanks Gasman. I appreciate you taking the time to respond.

I did begin to understand (as you explained) what Arnel had done. His delinking of the master and child links was intentional. Unfortunately the rest of the code is way beyond my limited Access knowledge.

 

[Gasman]

Can you explain the steps in how it is meant to work?
There is no code behing the Save button.

Any reason why the controls are unbound in the main form.?

 

[AdmiralN]

Can you explain the steps in how it is meant to work?
There is no code behing the Save button.

Any reason why the controls are unbound in the main form.?

The 'All' option was added to giver users a option to search for a record using the 'Search Activity' text box, rather than having to select the correct package No. under which the record is located first.

The save button appears when the option 'create new package' is selected in the combo box. Clicking on this should add a new package to the list. However, this does not work either at the moment.

I'm not sure why Arnel chose to have the controls unbound.

 

[Gasman]

OK, but please give the sequence steps on how it is meant to work.?

Eg
Create new package, Save, then add new records in subform?

 

[AdmiralN]

OK, but please give the sequence steps on how it is meant to work.?

Eg
Create new package, Save, then add new records in subform?

Yes that is correct.

However, the creation of a new package will be rare. The list of packages of packages that is there is unlikely to change.

Users will mostly be adding records under the exiting packages. So they first select the correct package from the combobox and then add the record.

 

[Gasman]

Well I found out that there is code for the Click event of the save button, you just lost the link, but I am having difficult finding bloc in All

 

[Gasman]

OK, I think I might have it working now, as my old version still had the link fields. :-(

It still needs a fair bit of work, but at least the package id is now being saved to the child table
Try the attached.

 

[AdmiralN]

OK, I think I might have it working now, as my old version still had the link fields. :-(

It still needs a fair bit of work, but at least the package id is now being saved to the child table
Try the attached.

Awesome! Thanks Gasman!