How do I fix my procedure code in Access 2013?

RichfordsIt
RichfordsIt used Ask the Experts™
on
In Access 2013, I have a form. this form takes a database and links it to another database. So that you can choose a code in a drop down menu and it will show the relative information in the other tables. When I switch from normal view to design view and then back to normal view. The procedure code seems to break and it doesn't show the information when I change the code. It will only work when I keep changing the variant to be a string in the procedure code, but it will break everytime i change the view.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
With no code, you leave us guessing.
However, it doesn't make that much difference, as your code should definitely not require you to switch between design view and form view.

You need to rethink your concept.

/gustav
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Yes, ...I too am confused by your "workflow" here.

Can you explain to us, ...with explicit details, exactly what this system is trying to do?

Perhaps there is a more efficient approach...

Author

Commented:
I am an apprentice in software development, so sorry if my description doesn't make sense.

So I have a form called form1, which has within it 3 sub forms: visit_by_job_replacement, by_job, equipment_needed. What I am trying to explain is in the sub form visit_by_job_replacement you can choose, from a drop down, a number of job codes. When you choose one of these job codes, the by_job and the equipment_needed is meant to change, which it does at the moment.

However, when i was changing from form view to design view and back. I noticed that it had stopped working, at this moment I was quite confused and went into procedure code and had a play around and noticed when I changed the id from a string to a variant it worked. So I went back and did the same thing, changed it from form view to design view and back. Again it stopped working so i changed the variant back to a string and it worked. I'm not sure if its a bug with access but it happens everytime i change from form to design and back.

The procedure code that I have is as follows:

visit_by_job1-Form_by_job(Code)

Public Function setParent(parent_id As Variant)
If parent_id = "" Then parent_id = "0"
Me.RecordSource = "select * from visit_by_job_replacement_c where parent_record_id = " & parent_id
Me.Requery
If Not (IsNull(Me!id)) Then Form_equipment_needed.setParent (Me!id)
End Function
--------------------------------------------------------------------------------------------------------------------------------------------
Visit_by_Job1 - Form_equipment_needed(Code)

Public Function setParent(id As Variant)
If id = "" Then id = "0"
Me.RecordSource = "select * from visit_next_visit_next_equipment_needed_c where parent_record_id = " & id
Me.Requery

End Function
------------------------------------------------------------------------------------------------------------------------------------
Visit_by_job1 - Form_visit_by_job_replacement(Code)

Private Sub Form_Current()
If Not (IsNull(Me!id)) Then Form_by_job.setParent (id)



End Sub
----------------------------------------------------------------------------------------------------------------------------------------------

I hope this helps in solving my problem. Even the IT specialists that have worked here for more than two years are confused as to whats going on with access.

Thanks.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Sounds like you - after a design change - should compile and save, close the form, and reopen it.

/gustav

Author

Commented:
It comes up with would you like to save these changes. I click yes go back into the form, and its exactly the same. It just breaks, until I click on a field that has a null value then it seems that the code starts to make a difference and change the other subforms.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Try this modification:
Public Function setParent(id As Variant)

    Me.RecordSource = "select * from visit_next_visit_next_equipment_needed_c where parent_record_id = " & Val(Nz(id)) & ""

End Function

Open in new window

The requery should not be needed.

/gustav

Author

Commented:
No there is no change, still does the same thing.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Then it has nothing to do with this code. Something else is going on.

If what you want is to sync the subforms, you may get some inspiration here:

Synchronizing Multiple Subforms in Access

/gustav
Went with re-designing the layout in the end; the 'main control' form is a Continuous Forms layout, so we cannot not add our two sub forms to it and had to use another parent form.
After trying unsuccessfully to set the record sources of the two child forms from the parent (wanting to avoid setting filters), I went the easy way and bound the parent to the same table as the main control form, setting its record source via the controller's onCurrent and used link master\child on the two sub forms (the third being a child of the second).

Thanks for your suggestions @gustav
/tom
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

/gustav

Author

Commented:
It works!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial