Access 2007 VBA Where value is populated on a certain row is true, update on same row

I don't know how to ask the following, but here it goes:

I have two forms.  On Form A, I have the end user enter in certain data - most importantly, their name and date.  This data is to be entered for the morning.

On Form B, I have their name and date to be entered as well, but the third cell is the afternoon data.

How may I go about creating VBA code or even a macro where when textboxes A and B on Form A are populated, it compares it to the data entered on Form B, and if true, the afternoon data is populated on the same row for that person and date.

Meaning, I don't want Form B's data on a separate row, I'd like for the entire information to remain together for that day.

Thank you.
kristibigoAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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

MuffyBunnyCommented:
What is the structure of the table this data is being written to?
0
Jeffrey CoachmanMIS LiasonCommented:
...and please post some sample data.
And post a graphical example of the exact output you are expecting, based on the sample data
0
kristibigoAuthor Commented:
Because I'm under a time crunch, and maybe the following will lead to a better solution, but I separated the table into two:  An AM table and a PM Table.  I then created a query linking the two tables via Senior Manager Name. Attached is the database.  My next dilemma (I think) will be the following:

Will the information to be entered from the AM form be on the same row of the query table when they later enter data in the PM form, and if not, what do I need to do where Senior Manager name and DateAM equals Senior Manager Name and DatePM?

And last but not least, how do I make it where only the "Entering Decisioning/L14 Data" form opens without the Access window?

Thanks.
Decision-DB.accdb
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Jeffrey CoachmanMIS LiasonCommented:
What is your time constraint?

<maybe the following will lead to a better solution, but I separated the table into two:  An AM table and a PM Table.>
This probably makes this more difficult

You have a reference to a control that we don't have
Your form appears to be based on an non-updateable query.

This may take us a while to work through without an intimate knowledge of why this was designed in this way.
Also there is no real reference to the raw data or what the output should look like.

In other words, this site is not really a site where you can just can't "Post" a database and ask us to fix it...

JeffCoachman
0
kristibigoAuthor Commented:
Yes, I understand.

My time constraint, well, I was hoping to have this up and going by today, Thursday.  But I'll take any solutions even if it's Friday or Monday.

Basically two things are happening.  The end user is inputting data in the morning, and then they are inputting data in the evening.  If the afternoon data cannot be updated on the same row as the morning value, then I'll make due.  That's my issue: how do I have the data from the latter entry be on the same row as the earlier entry.  That's all.  

When the end user clicks on the "Enter" button, it saves the record.

Then because I just want the end user to enter the data and not be bother with any of the Access details, I was curious if there was a way for the "Entering Decisioning/L14 Data" to only show when you click on the shortcut - or what have you.

What control do you not have access to that I may be able to turn off?

Thanks.
0
kristibigoAuthor Commented:
I went back into the database to see what I could figure out, and the run time error you mentioned happened to me  - the recordset is not updateable.  It's apparently how I've set up my module.  I've taken them out.  

Basically what I want is when you click on the "Open Morning Data?" button, I want the next form to have all blanks for the end user to fill in.  I see a lot of other issues I thought I had resolved, but apparently didn't save.  So I'm once again uploading the database.  My apologies for the inconveniences.
Decision-DB.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
<Basically what I want is when you click on the "Open Morning Data?" button, I want the next form to have all blanks for the end user to fill in.>

Is this all you want?
Then add this to the macro:

untitled.JPG
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
kristibigoAuthor Commented:
Okay - I did try to look for where you put that condition.  Would you give me the steps to place this information?  I feel silly that I can't locate it, but it's probably because I'm making it more complex when it's probably a simple step.

Also, I guess there's no way to have the data updated on the same row where Senior Manager Name and Date are the same - AM vs PM data?  I could go back and make it one table instead of a query, but it still wouldn't solve keeping the same name and date on the same row from AM to PM.

But the other question was how to have the initial form only open without the Access window.  Is there a simple process for this request or is there a link on the Experts Exchange that will give the VBA code? Or?

Thank you for your time.
0
Jeffrey CoachmanMIS LiasonCommented:
Open the AM Data form in design view
Right-click on the button
Select: Build Event
0
kristibigoAuthor Commented:
It still doesn't start all the textboxes with a blank field when the form is opened. I'm also receiving this message box after I altered the macro in the Build Event (see attached image):

 The command or action 'SaveRecord' isn't available now.
0
kristibigoAuthor Commented:
Can we start again? I had put the Build Event not with the button but with the form. Thus, the error message. However, I'm still experiencing the first field not to be blank when  you open the form.
0
Jeffrey CoachmanMIS LiasonCommented:
The source for your form is a non-updateable query, so you will have to fix this issue first.
0
kristibigoAuthor Commented:
Yep. Okay, take three.  I'm uploading the database once again. If I need to re-submit this question, I will.  I really do try to have things figured out before I submit them.  It seems I just made a mess and figured you could clean it up.  That wasn't my intention.  Attached is a revised version of the database.  I removed the query.  Created one table with all the data.  Fixed the forms so they would open properly. And hopefully the things I need resolving can now be handled. So, the two things I'm hoping to get resolved (if possible) are:

Once the AM data is entered and saved and after the PM data is entered and saved, where the Senior Manager Name and Date match, have that manager and date's data be on one row; and,

Have it where only the "Entering Decisioning/L14 Data" opens when you click on a shortcut/icon, or what have you.

Thanks.
Decision-DB.accdb
0
Jeffrey CoachmanMIS LiasonCommented:
Then the solution is what I posted above, ... add the new line to the macro.

What is odd is that you have a "Close" command in there, I don't know why it is there, but you should remove it...
0
kristibigoAuthor Commented:
Okay, that's now storing the data like it should.  However, the PM data is not storing on the same line as the AM data.  The close function I moved to the end. I want the form to close once the information is entered.

Is there a way for the "Entering Decisioning/L14 Data" to open only as a form without the Access window? Decision-DB.accdb
0
kristibigoAuthor Commented:
Needed other help on how to store data on the same row of a table and to open the form without the Access windo.
0
Jeffrey CoachmanMIS LiasonCommented:
<Needed other help on how to store data on the same row of a table>
I am still confused on why this system is designed in this way...

<open the form without the Access windo>
This was not part of your original question...
...but see here in any event...
http://www.mvps.org/access/api/api0019.htm
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.