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?
 
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
 
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.