How do I fix my procedure code in Access 2013?

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.

Jeffrey CoachmanMIS LiasonCommented:
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...
RichfordsItAuthor 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:


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
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

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.

Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

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

RichfordsItAuthor 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.
Gustav BrockCIOCommented:
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.

RichfordsItAuthor Commented:
No there is no change, still does the same thing.
Gustav BrockCIOCommented:
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

RichfordsItAuthor Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
You are welcome!

RichfordsItAuthor Commented:
It works!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.