Textbox Value to another Textbox

[gsandy]

I have a date textbox (Week_Ending) and number textbox (Staff_ID) in a form (frmHourEnter), when both have values I open another form (frmStaffReport) with textboxes (txtDateStart and cmbStaff).
How do I open the second form with the values of the first form pre-entered? Thanks Sandy

 

[csh2013]

The tables need to be linked together in some sort of relationship and there would need to be something to tie the fields together correctly to apply the appropriate values from one table to the rows from the other table. I think posting an example of your current database setup would probably be helpful in this situation.

 

[gsandy]

Attached is shorten version of database. Select a staff member, pick a date and hit the command button. You will see the second form I want the data transferred to.

 

[csh2013]

I'm not 100% sure some of what you're trying to accomplish. So, here's what I did. Your form "frmStaffReport" isn't bound to anything. I set the record source of the form to tblStaff and then bound the control for the Staff field to StaffID and the control for Start Date to DateEmployed. After that it's just a matter of the right VBA language to open the form itself. So, in the OnClick event for the Staff Report button, I used

DoCmd.OpenForm "frmStaffReport", , , "[StaffID] =" & Me!Staff_ID

I'm not sure if that's exactly what you wanted to accomplish, but it seems to me that the frmStaffReport should be bound to a record source rather than just having all unbound controls that you would need to need to write lookups for.

 

[gsandy]

Thanks csh. The staff name worked just like I wanted it to.
My labeling and naming for the date on frmStaffReport was not good! The label for txtDateStart should read "Week Ending" and its textbox (txtDateStart) should get the date from the form (HourEnter), textbox (Week_Ending). Can you help achieve this?

 

[csh2013]

Okay, well then, if I were you (and again, this is just me), I would assign tblHourEnter as the record source for frmStaffReport then you can bind the Week_Ending information to the start date box and you can still assign the Staff ID to the Staff field, all while retaining your lookup function

Within this form (frmStaffReport), I'm not sure how you intend to display the "Staff" box. When I applied the record source, it retains the settings you previously assigned to it in the table, which is the 3-column setting for ID, FirstName, LastName. As I don't work with the multi-column option very often, I'm don't know there is a way to write the code so that it looks only at the ID during the openform command. So, I can only provide one of two options here:

#1 - You change the column count of cmbStaff to a one column field and only display the ID #. In that case, your VBA would read:

DoCmd.OpenForm "frmStaffReport", , , "[cmbStaff]= " & Me!Staff_ID

#2 - if you need to see the 3 columns displayed, instead of limiting your column count in cmbStaff, add another text box onto your form with the record source being the 1-column Staff ID. You can set the visibility set to No if you don't want it displayed. When I did that, the control was auto-assigned a Name of Staff_ID and the VBA was:

DoCmd.OpenForm "frmStaffReport", , , "[Staff_ID]= " & Me!Staff_ID

 

[gsandy]

Thanks again csh. The first part works fine but I am still having problems with the date part. The date I want transferred to the second form (which is now bound to tblStaff) only exists on the first form, not in any table. As an interim measure I am retaining the date selection textbox which just means that the operator has to select a date rather than having it pre-entered. If you have any other ideas let me know. Cheers Sandy

 

[gsandy]

I will close this question and reissue the last part.

 

[csh2013]

My suggestion was to use tblHourEnter as the record source of the second form because the date information is available in tblHourEnter.

 

[gsandy]

tblStaff is current record source and the name transfers from the first form OK. If I change the record source to tblHourEnter the name and the date does not transfer. I do get a "Enter Parameter Value" message.

 

[csh2013]

When I go into the database you uploaded and open frmStaffReport, the property sheet shows the record source as blank. If you directed it toward tblStaff, that must have occurred after you uploaded that copy. And without knowing specifically what other changes you made, I wouldn't know how to provide you with any additional direction.

Just as one follow-up piece, however. I neglected to change the code from the original issue when I assumed the "Start Date" was the employee's date of hire. Because you want the secondary form (frmStaffReport) opening the record of the date you selected, you would need an additional WHERE clause with the openform command to select the record that contains the week ending date the user already selected on the first form. My apologies - I completely overlooked that on the second pass through.

 

[csh2013]

Last piece. I didn't want to leave you hanging with my previous comment about needing an additional piece to a WHERE clause, but I didn't have the time to verify it worked until now. If the frmStaffReport is tied to tblHourEntry, your openform command will look like this:

DoCmd.OpenForm "frmStaffReport", acNormal, , "[Staff_ID] = " & Me!Staff_ID & " AND [Week_Ending] = #" & Me!Week_Ending & "#"

You may also want to consider alternatives to all the DLookup functions - they're causing a lot of unnecessary bloat in your database.

Good luck with your database!

 

[gsandy]

I still could not get it to work. But this is my first database, it has been a steep learning curve. I find vba/Excel easier!
I did solve the date transfer problem by storing the date in the first form as a public variable and recalled it in the second form. So problem solved.

The only reason I use DLookUp is because its the only way I know to get the full staff name to appear rather than just the staff ID number.

Once again thanks for your input. Cheers Sandy.