Solved

Set Subform Value

Posted on 2014-10-02
7
91 Views
Last Modified: 2016-06-26
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!
0
Comment
Question by:Lee W, MVP
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 10

Expert Comment

by:t_hungate
ID: 40356980
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
 
LVL 96

Author Comment

by:Lee W, MVP
ID: 40357064
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
 
LVL 38

Expert Comment

by:PatHartman
ID: 40357242
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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 96

Author Comment

by:Lee W, MVP
ID: 40357314
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
 
LVL 38

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40357328
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
 
LVL 30

Expert Comment

by:hnasr
ID: 40359318
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
 
LVL 27

Expert Comment

by:MacroShadow
ID: 41674831
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

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question