Take data from 1 table and place it in a new table

[Bsorensen]

I have created a table: TblDVAssociate with 2 fields:
Field 1 Name
Field 2 Pin <--This is also the Primay Key as I do not want duplicate Pin Numbers

I have another table: TblMaster with 6 fields:
Field 1 ID (Auto Number)<-- This is the Primary key
Field 2 AssociateName
Field 3 ProjectName
Field 4 Totals
Field 5 TimeStart
Field 6 TimeStop

I have a table:TblProjec ts with 3 fields
Field 1 Master Number (AutoNumber) and Primary key
Field 2 Project Name
Field 3 Per Hour Goal

I am using a form (FrmProject) to update TblMaster:
The form has a field for Associate Name

A Combo box for Project name which is tied to TblProject allowing a specific project to be selected which in turn places the name of the selected project into the field ProjectName on TblMaster

A totals field which updates teh Totals field on TBLMaster with a number

A TimeStart and TimeStop field which I use a command button to enter the start time and another command button to enter the end time into TblMaster.

And finally an Update button which activates a macro to advance the record and place the collected data in the Tbl Master (this was done because I do not want people to be able to scroll through and see other data entered, but I needed a way to tell teh form to write to the TblMaster.

Now, for my question.

What I want to do is create a pop up which asks the associate to enter their "PIN", this will then grab the associated name from TblDVAssociate and place it on the FrmProject so that the associate sees their name and it will update into the AssociateName field on TblMaster.

I have spent the last few days trying to work out how I can make this happen but the answer eludes me.

I was able at one point make the AssociateName field on the form populate a name from the TblDVAssociate when the Pin was entered but it did not place the name on the TblMAster, as soon as I selected a Project Name fromm the drop down, the name vanished. (and subsequently, I have not been able to reproduce this so I am a little lost.

[yarbrough40]

I think what you want for your popup box is an InputBox

Dim answer As String
answer  = InputBox("What is Your Pin?")

'then run your query to your TblDVAssociate 
"Select Name from TblDVAssociate where Pin = '" & answer & "' "

[Bsorensen]

Thank you Yar, Not quite what I am looking to do.

I have attached screen shots showing the components of my database, the screen shots also have notes on them trying better to explain the flow of what I want to do.

Start with FrmTheButton Start Here.jpg, then go to frmProject and build screens associated.JPG then proceed to: frmProject and build screens associated2 Tables.JPG so you can see my 2 tables.

As Described in the Screenshot Frmproject and build screens associated, I am looking to create a flow as follows:
Open FrmtheButton--
Enter Pin--
See name in FrmThebutton--
Click Button Start Work which then--
Opens FrmProject--
Name from FrmTheButton Populates in AssociateName Field--
Select Project--
Click Start Work Button on FrmProject--
Enter Totals--
Click End Work Button--
Click Update.

At this point I want to be able to open TblMaster and see The Associate Name, ProjectName, Totals, Start Time, End time.

Everything works as I want right now Except for the Name updating from FrmThebutton to FrmProjects and subsequently to TblMaster.

Please see the attachments and let me know if there is any help for me, or if it would help to see anything else.

If the Screen shots are not readable I can try the Bitmap version instead of the Jpeg version, but they are larger in size (significantly) .

Brian

PS Thanks again Yar. :-)

[yarbrough40]

what method have you tried to use to send the button form data to the project form.. can you show me some code?

[Bsorensen]

I do not have any current code to offer.

My last attempts were starting from FrmProject, entering design view, right clicking on Associate name selcting properties then in the Control Source field selecting the Elipsis which brought up the Expression Builder.

This populates with AssociateName below are three sets of boxes/windows, I start in the first and select Forms/All Forms/frmthebutton.

The next box then populates with:
<form>
<field List>
CmdBreaklunchBu tton <this is actually the Start Work Button I just have not renamed the coding name yet, all it does is activates an Open form Macro>
Label5
Name
Detail

If I choose <fieldlist> I see in the third box Name and Pin

This is the most recent way I have been trying to go, but I think I am heading int he wrong direction.

let me try to ask my question this way (my mind keeps looking for solutions)

Lets forgot about Frmthebutton (I think it becomes an overcomplicated element, and extra work.

If I wanted to Open a frmProject and before it opened all the way I want it to ask for a "PIN" once entered it would take this Pin and look up the corresponding name from TBLDVAssocName and place it on my frmproject and on my table TblMaster,

What Should I try?

Thank you very much Yar for sticking with me on this and offering your help. I am trying to give you useful information and trying to be as clear as possible.

[yarbrough40]

ok so it looks as though you are not very familiar with VBA and how to use it to accomplish tasks in Access am I correct? what you are attempting to do requires that you begin writing VBA code. You will be hard pressed to find anyone in this forum who will take the time to walk you through this as that is not what we are all here to do. What we do here is help you with general directions and even help debug and suggest code and functionality but only after you have gone down this route and are stuck.

I would suggest googling "Acess VBA tutorial"

I will throw you a couple of bones on this, however.

1) stear away from using macros and learn "Event Procedures" instead
2) what you want to do specifically should probably folow this format-->
frmthebutton should have a button in it - write your code in the OnClick() event of this buton to:
a) run the first part of the code I supplied to you earlier (inputbox)
b) use the value of that inputbox as a variable in a "Query" that you build to retrieve the Name associated with the ID number
c) open FrmProject and place the result of the Query into the textbox on the FrmProject.

[mshmyob]

A couple of things wrong with what you are doing:

If I wanted to Open a frmProject and before it opened all the way I want it to ask for a "PIN" once entered it would take this Pin and look up the corresponding name from TBLDVAssocName and place it on my frmproject and on my table TblMaster,

1st you cannot do this. You cannot start to open a form and somehow magically stop the load process to ask for input and then continue loading and take that variable and insert it into something in the form.

You would need to prompt for an input (Yar already mentioned how) and then pass that variable to your opened form or pass it as you are opening the form using the 'openargs' option of the openform method.

2nd and more important - You should normalize your database. None of your tables are even related to each other or normalized. read up here on normalization.

cheers,

[Bsorensen]

Thank you Yar and mshmyob. I apreciate teh help and candidness.

Yar you are right I know little VBA, thank you for teh tutorial link.

mshmyob, thank you for the link to normalization, I will be reading it promptly. Thank you both for the help and suggestions.

Please have a great day.

Brian