UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> New Record Do This, Access 2007    
 
   
Leah
post Jun 19 2017, 09:58 AM
Post#1



Posts: 665
Joined: 22-February 00
From: New York, New York


I have a field A that defaults to 'Unknown' when a new record is created and that is just fine where that is a required field for a particular client's detail records. However for some clients marked with field A required no, upon creating a new record I want that field updated to 'N/A'. I don't know how to do that one time update. Please advise. The new record would usually be added via a form, but might also be uploaded from Excel.

Thanks

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
theDBguy
post Jun 19 2017, 10:04 AM
Post#2


Access Wiki and Forums Moderator
Posts: 70,670
Joined: 19-June 07
From: SunnySandyEggo


Hi Leah,

Not sure I understand... If you're storing the data in the same table, then Field A would either be marked required or not; there's no need to check for one set of data or the next. So, what do you mean by some clients mark Field A required and others don't?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Leah
post Jun 19 2017, 10:47 AM
Post#3



Posts: 665
Joined: 22-February 00
From: New York, New York


Sorry, the client table has a one to many relationship with the Detail table.

When a new field is added to the detail table then the field is defaulted in the detail table to Unknown. Later the end user will have to choose from a drop down to update it. For a few clients that field is not used and does not appear on the form for them so I want it to update that to N/A where the client record indicates that the field is not required. I do not want to leave it as Unknown because the records are not considered "completed" until there are no "Unknowns."

Thanks

Leah



--------------------
Leah A. Kopel
Go to the top of the page
 
BuzyG
post Jun 19 2017, 11:01 AM
Post#4



Posts: 303
Joined: 20-September 12
From: Cornwall UK


How is the new data being added to your existing tables. Is is via an Update Query?

--------------------
Live to Surf
Go to the top of the page
 
Leah
post Jun 19 2017, 11:09 AM
Post#5



Posts: 665
Joined: 22-February 00
From: New York, New York


It is being added either by an append or someone adding a new row at the asterisk. It is really the asterisk one that I can concerned with as I could put the field in with an if statement in the append.

--------------------
Leah A. Kopel
Go to the top of the page
 
theDBguy
post Jun 19 2017, 11:31 AM
Post#6


Access Wiki and Forums Moderator
Posts: 70,670
Joined: 19-June 07
From: SunnySandyEggo


Hi Leah,

I'm sorry but without seeing what you have, I am having a hard time following your requirements. Can you post images or a sample copy of the db?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
Leah
post Jun 19 2017, 11:33 AM
Post#7



Posts: 665
Joined: 22-February 00
From: New York, New York


I just found something on the web called a new record property. I will try to play with that and see if I can make use of it.

--------------------
Leah A. Kopel
Go to the top of the page
 
BuzyG
post Jun 19 2017, 11:34 AM
Post#8



Posts: 303
Joined: 20-September 12
From: Cornwall UK


How does the client mark a record as not required?

After a manual entry, on a form, you could use after update to test the contents of the Me![fieldname], If me![fieldname] ="not required" Then me![fieldname]="N/A" and update it again.

Edit

Ok think I understand now. The field property in some of your clients databases is set to not required and you wish to mark those fields as N/A for, just those clients in your database. Is that about right?
This post has been edited by BuzyG: Jun 19 2017, 11:44 AM

--------------------
Live to Surf
Go to the top of the page
 
Leah
post Jun 19 2017, 11:36 AM
Post#9



Posts: 665
Joined: 22-February 00
From: New York, New York


Thanks, I will consider that too.

--------------------
Leah A. Kopel
Go to the top of the page
 
Leah
post Jun 19 2017, 12:01 PM
Post#10



Posts: 665
Joined: 22-February 00
From: New York, New York


I queried the Client table for the specific Client and called it "Field_Name Required."

I had never heard of the on current property, probably because I usually look at field properties.

Private Sub Form_Current()

If Me.NewRecord = True Then

If DCount("*", "Field_Name Required") = 0 Then

Me.Field_Name = "N/A"

End If

End If

End Sub

This worked. Any dangers in doing it this way?

Thanks

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
BruceM
post Jun 19 2017, 12:50 PM
Post#11


UtterAccess VIP
Posts: 6,832
Joined: 24-May 10
From: Downeast Maine


QUOTE
I queried the Client table for the specific Client and called it "Field_Name Required."

What specific client is that? If you go to a new record, there are no data in any of the fields. Do you mean you decide which client is going to be the subject of the new record, build the query to show the records for that client, then start a new record and use DCount based on that query? Maybe I'm missing something obvious, but I really have no idea what you are trying to do.

But you asked about risks. Here is one: every time you go to a new record and there are records in the query it will enter "N/A" into Field_Name. If you decide not to enter the record, too late. The value has already been written.
Go to the top of the page
 
Leah
post Jun 19 2017, 01:38 PM
Post#12



Posts: 665
Joined: 22-February 00
From: New York, New York


The client code is entered based on the current client listed in the dropdown. The DCount query is based on the client in the dropdown and then updates the Me.Field_Name accordingly. There is only one new record at a time so only that record gets updated.

Apparently I had the rule to update it using this Dcount in some of the field's after update properties and it had been working. However the end users had me change a number of things in the client requirements table so it may be that I neglected to update everything in the detail tables accordingly.

Just wanted to know if anyone uses the on current property regarding new rows and if they have had issues with it as I think I might want to make more use of it.

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
BruceM
post Jun 19 2017, 01:48 PM
Post#13


UtterAccess VIP
Posts: 6,832
Joined: 24-May 10
From: Downeast Maine


QUOTE
The client code is entered based on the current client listed in the dropdown

What dropdown is that?
QUOTE
Just wanted to know if anyone uses the on current property regarding new rows

Yes, many people, assuming you mean new records. Keep in mind that the Current event fires as soon as you arrive at a record, and not again until you go to another record. If you select a client, that action will always occur after the Current event.
Go to the top of the page
 
Leah
post Jun 19 2017, 02:02 PM
Post#14



Posts: 665
Joined: 22-February 00
From: New York, New York


Thanks everyone for your help on this.

Leah

--------------------
Leah A. Kopel
Go to the top of the page
 
theDBguy
post Jun 19 2017, 02:08 PM
Post#15


Access Wiki and Forums Moderator
Posts: 70,670
Joined: 19-June 07
From: SunnySandyEggo


Hi Leah,

Glad to hear you got it sorted out. Sorry but I just did not feel comfortable offering any advise without fully understanding your needs and risk telling you to do something wrong. Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    23rd September 2017 - 06:40 PM