#Error

[cmray58]

I have a main user input form ("CALS v2" in the attached copy). Within that form is a command button that opens up the current record in a secondary form ("HelplineReferralTracking"). Once the user inputs what they need in Helplinereferraltracking, they click the save and close button and it brings them back to CALS v2 to the same record. However, when they return to CALS v2, the calculated fields return #ERROR

 

[June7]

How can the second form open to the same record? These forms have different data source.

Why don't you use subform for referral tracking data entry?

A client can have multiple referral records?

Advise not to use multi-value fields. Use a related dependent table (which is what Access is doing but hiding).

Consider using a tab control for more compact arrangement.

 

[cmray58]

How can the second form open to the same record? These forms have different data source.

Why don't you use subform for referral tracking data entry?

A client can have multiple referral records?

Advise not to use multi-value fields. Use a related dependent table (which is what Access is doing but hiding).

You're correct, it doesn't open to the same record, it creates a new record in another table based on the client's info in the original form. I'm doing this for reporting purposes. Yes, a client can have multiple referral records if they call multiple times. I'm not using multi-value fields.

Getting back to the original question, do you know what causes #Error?

 

[June7]

Yes, you have a multi-value field for Diagnoses.

Not yet.

Use a form/subform arrangement and Access will do work of relating records.

 

[cmray58]

Yes, you have a multi-value field for Diagnoses.

Cals v2 (advise not to use spaces in naming convention) code passes ID to referral form but you do not populate foreign key field with that value.

Use a form/subform arrangement and Access will do that work for you.

Diagnoses is not being used anywhere, I just haven't deleted the field from the table yet.

Code passes ID to referral form, and the referral form using the ID upon open from the open args. Unless I don't have a full understanding of subforms (which is more than likely), I don't want the referral form to be on the same screen as CALS v2, because it'll be too large.

 

[June7]

Looks like you read my previous post before I edited it.

Size of forms is why I suggested a Tab control.

Code would be much easier to read if you used indentation.

Cals v2 form opens fine from Navigation form or direct from Navigation pane but something odd happens when Cals v2 opened by referral form, with or without filter criteria. Works fine executed from Immediate Window or setting Filter and FilterOn properties in design.

I set a breakpoint on the End If in Command42 and run code. I can see calculations on Cals v2 form. All except the Text848 message calculate. I let code finish executing. Calcs are still showing but as soon as I scroll form they all go to #Error! except Text848 which still has #Type! error. Removing Text848 calc does not help. However, open without filter criteria from referral form and Text848 does calc, others show #Error!. Clicking RefreshAll on ribbon triggers error "There is no primary key defined" and debug stops on first line of Current event. I even tried changing code so Cals v2 is not closed/reopened and calcs still error, except Text848. I am at total loss as to why this fails.

So far, I have modified code as follows:

Form [Cals v2]

Private Sub Command1328_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "HelplineReferralTracking", , , , , , Me.ClientID
'DoCmd.GoToRecord acDataForm, "HelplineReferralTracking", acNewRec
DoCmd.Close acForm, "CALS v2"
End Sub

Form Helplinereferraltracking

Private Sub Command42_Click()
If MsgBox("Would you like to save and close?", vbYesNo + vbQuestion) = vbYes Then
    If Not IsNull(Me.OpenArgs) Then Me.ClientID = Me.OpenArgs
    If Me.Dirty Then Me.Dirty = False
    DoCmd.OpenForm "CALS v2", , , "ClientID = " & Me.ClientID
    DoCmd.Close acForm, "Helplinereferraltracking"
End If
End Sub

Private Sub Form_Load()
If Not IsNull(Me.OpenArgs) Then
    DoCmd.GoToRecord , , acNewRec
End If
End Sub

 

[cmray58]

Hello, revisiting this....I'm still unable to fix #ERROR

 

[June7]

Having another look.

Purpose of UNBOUND textboxes on form CALS v2 are just to show users which controls require entry? Instead of setting value of textboxes to asterisk or empty string, why not just labels and set visibility? Or even if you use textboxes, just have ControlSource of ="*" and set visibility in code. Consider this alternate code:

'If Assessor name is blank
[AssessorName].FontItalic = [AssessorName] = "Please Select One..."
[Text473].Visible = [AssessorName] = "Please Select One..."

'If required fields are blank
[Text405].Visible = IsNull([LastName])

Not really seeing need for public variable IngPK. In fact, the RecordsetClone code makes no sense. Why would you need to search for record that already has focus? Code sets IngPK to Me.ClientID then searches for that same ClientID.

BeforeUpdate event in Referral form is not needed.

Every code module should have following two lines at top in header section:
Option Compare Database
Option Explicit
To enforce Option Explicit, in VBE > Tools > Options > check Require Variable Declaration.

I still don't understand why opening CALS v2 from Referral causes this issue. I even tried code that instead of filtering, uses Bookmark code to go to record.

Possibly use of form/subform would resolve. A Tab control could organize everything into much smaller area.

 

[June7]

I discovered that when Referral form and its controls are UNBOUND, opening CALS v2 from Referral code does not cause #Error on CALS v2. If you insist on this arrangement, looks like have to use UNBOUND Referral form and INSERT action SQL to save new record.

I tested a form/subform and that seems to eliminate issue.

Sorry, cannot explain why this happens. Never encountered this before. I tried to replicate in a db with my data and calculated field in RecordSource and couldn't. I did not explore your calculations in detail.

 

[cmray58]

I discovered that when Referral form and its controls are UNBOUND, opening CALS v2 from Referral code does not cause #Error on CALS v2. If you insist on this arrangement, looks like have to use UNBOUND Referral form and INSERT action SQL to save new record.

I tested a form/subform and that seems to eliminate issue.

Sorry, cannot explain why this happens. Never encountered this before. I tried to replicate in a db with my data and calculated field in RecordSource and couldn't. I did not explore your calculations in detail.

Hi June, I appreciate the continued support. Would you be able to summarize the steps you took to do both of the above? I'm not familiar with how to use the unbound method. This sounds like something that may work for me. I completely realize that this is by no means an ideal setup, but honestly we're past that point right now.

I've used a form/subform setup before, but the only way I'm aware of is by splitting the tables into separate tables and relating them together. Is that what you did?

 

[June7]

UBOUND method would just be UNBOUND controls on form then use SQL INSERT action to save record to table. VBA syntax to run INSERT statement for number, text, date fields:

DoCmd.Execute "INSERT INTO tablename(Field1, Field2, Field3) VALUES(" & Me.tbx1 & ", '" & Me.tbx2 & "', #" & Me.tbx3 & "#)"

You already have the 2 related tables needed for form/subform: CALS and HelplineReferralTracking.

 

[cmray58]

UBOUND method would just be UNBOUND controls on form then use SQL INSERT action to save record to table. VBA syntax to run INSERT statement for number, text, date fields:

DoCmd.Execute "INSERT INTO tablename(Field1, Field2, Field3) VALUES(" & Me.tbx1 & ", '" & Me.tbx2 & "', #" & Me.tbx3 & "#)"

You already have the 2 related tables needed for form/subform: CALS and HelplineReferralTracking.

Thanks again. I'm attaching an updated copy of the DB. I've embedded the referral tracking form as a subform in CALS v2. They seem to be linked by ClientID. However, when you enter data in the referral subform, the data is saved in the referral table without recording the ClientID.

Ignore the placement, format of the form right now. Just working on functionality.

 

[June7]

Were you on an existing record of main form? Did you enter data into new record of main first? If you entered data into subform without parent record on main form, record was allowed because there is no referential integrity set in relationships.

 

[cmray58]

Were you on an existing record of main form? Did you enter data into new record of main first? If you entered data into subform without parent record on main form, record was allowed because there is no referential integrity set in relationships.

Ah, yes. It was while entering a new record. Would I need to first have the new record in the main form save before entering in the subform?

EDIT: I take that back. The new record is being saved, and I do see the relationship with referential integrity on. Not sure why it's not carrying the ClientID over?

EDIT2: The ClientID is carrying over to the referral subform, evident by the ClientID field in the form itself. However, when saving the record, the ClientID disappears.

 

[June7]

Oops, I looked at the wrong relationship. That is odd. Another mystery. I don't know why the orphan record is allowed.

Need to modify code. There is no need for close and save button on subform. The HelplineReferrals button is no longer relevant.

 

[cmray58]

Oops, I looked at the wrong relationship. That is odd.

Need to modify code. There is no need for close and save button on subform. The HelplineReferrals button is no longer relevant.

Right, agreed with both. Haven't removed those yet because again just trying to get the function right first. I haven't been clicking the Save and Close button on the referral form but instead using the Save action from the ribbon. I've tried a few different permutations and cannot get the ClientID to save with the record. Even if it's an existing record on the main form.

 

[cmray58]

Oops, I looked at the wrong relationship. That is odd. Another mystery. I don't know why the orphan record is allowed.

Need to modify code. There is no need for close and save button on subform. The HelplineReferrals button is no longer relevant.

GOT IT...I think. I had to remove the Before Update event, which was pulling the ClientID from the OpenArgs when it was a standalone form.