Set Subform Value

I THINK this should be easy... but...

Have a form with a subform.  

The Form references a customer, the subform references a location (Customers could have more than one location).

Users were doing things to create blank records, so I set the state field on the location address to NOT NULL (SQL Backend).  Now, when adding a new customer, it's automatically trying to add a location.  

I need to either:
Stop the creation of a new Location automatically when creating a new customer (no clue how to do that).
OR
When someone clicks on the New Account button (Macro based, not Code based) how I can have the Macro based button automatically set the state value so as not to cause an error to pop up about NULLs.

Your assistance would be greatly appreciated!
LVL 98
Lee W, MVPTechnology and Business Process AdvisorAsked:
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.

Tony HungateDirector of TrainingCommented:
Can you provide your table structure for clarification?

I am assuming there is a Customers table with a one to many relationship to another table containing locations but it sounds like you would want to add your customer first, then subsequently open a or access a method for inputting location data.

Maybe break down the addition of new customers into 2 steps, create new customer and populate Customers table, then open your form for location using your customer primary key.

You can also suppress the warning message if the form is loading properly and you just need to allow the user to access the location from to correct the NULL issue.

DoCmd.SetWarnings False

Open in new window


Hope this is somewhat helpful, more info is needed to be more exact.
0
Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
Sorry, I thought I was pretty explicit.

Yes, Customer gets added first (why would you add a location customer's location before adding the customer?  Sorry, this logic escapes me).

Workflow cannot be significantly changed.  The database was existing and I came in to take over its development.  I'm good (not great) with access.
Illustration of Form
0
PatHartmanCommented:
To prevent subforms from trying to add records before the parent record is added, you need some code in the subform's BeforeInsert event.  Here's an example from on of my applications.  Check the autonumber on the parent form and if it is null, undo the typing in the subform and cancel the update.  I also move focus to the combo on the main form that is used to select a client but you may want to do something else.
Private Sub Form_BeforeInsert(Cancel As Integer)
    If Me.Parent.txtClientID & "" = "" Then
        MsgBox "Please select a client first.", vbOKOnly
        Cancel = True
        Me.Parent.cboFindClient.SetFocus
        Exit Sub
    End If
End Sub

Open in new window


If subform records are being added without the user typing anything in them at all, then it is your own code that is dirtying the record and causing the problem.  You will need to examine the events of the subform to see where the problem is.  Post the code and we'll help you.

I know you asked for a macro but I don't do macros.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Lee W, MVPTechnology and Business Process AdvisorAuthor Commented:
I don't do Macros either.  (I took over the project and am adjusting things where I can).

Thanks for the suggestion.  I'll look into how I might be able to incorporate your solution.
0
PatHartmanCommented:
You really need to find the macro that is dirtying the subform and kill that.  Good Luck.

Personally, I would probably start by converting all the macros to VBA.  It will be crummy VBA but it will be easier to work with than macros.
0

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
hnasrCommented:
In the sub form, delete the field linking the sub form record to the Main record.. In this case a sub form record is created only for the current parent record.
0
MacroShadowCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Accept: PatHartman (https:#a40357328)

If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

MacroShadow
Experts-Exchange Cleanup Volunteer
0
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.