Putting a default field value into subform from main form...

[jhko]

Hi Gang:

OK, I've spent a couple of hours searching Google and trying to figure this out by myself and I'm stuck. Any help would be greatly appreciated.

I have a very simple database, two tables. EmployeeInfo is one table, EmployeeReviews is the other table. Since one employee can have many reviews, there is a one to many relationship between the employee table and the reviews table, and they are related through a PayrollServiceID field, which is the primary key in the EmployeeInfo table and the foreign key in the EmployeeReviews table.

I have a form setup to scroll through the employees in the employee table. I also have an employee review form set up for the reviews table.

On the EmployeeInfo form is a button to add a new review for that particular employee. I want the button to open the EmployeeReview form in a manner that it is ready to add a new record, and I want the payroll service ID field on the EmployeeReview form to default to the value that was currently displaying in the PayrollServiceID field in the EmployeeInfo form when you pushed the button, so you don't have to retype this info.



This must be stupidly easy, but I can't figure it out. Thanks again in advance.

[June7]

Consider a form/subform arrangement, no button and no code required.
http://office.microsoft.com/en-us/ac...010098674.aspx

[jhko]

Thanks so much for the response. That almost solves the problem. But here is a wrinkle. I guess I wanted to make the subform only visible if a button was pushed. Ideally, the subform would be password protected. Can this be accomplished using the form/subform arrangement?

[June7]

Jeez, you really want to code something. Set the subform container Visible property to No. Then code behind the button Click event can prompt for a password and set Visible to Yes. Will each user have own password stored in a Users table?

[jhko]

Come on June7, real men never want to build something easily using off-shelf-tools when they can spend five times as long and waste five times the money doing it themselves!

Thanks, that's what I was thinking. The visible property would work. A single password is probably OK, so I think I can just hardcode it. I don't understand the Users table approach yet

Thanks very much for your help.

[jhko]

OK, got it to work pretty well now. Here's what I came up with. This works, but if anyone has code improvement suggestions, I'm all ears. It seems like the code could be simplified. I'm still a real newbie at this:

Dim password as string 'in declarations area

Private Sub Command36_Click()
'check if password has been entered previously
If password = "passxxx" Then
'toggle the form visible/invisible with button push
Me!ReviewsSubform.Visible = Not Me!ReviewsSubform.Visible
Else
'If password not set, ask for it
password = InputBox("Enter Password")
'If password not correct, let them know
If password <> "passxxx" Then
MsgBox ("You're not authorized")
End If
'If password correct, let them know they are logged on
If password = "passxxx" Then
MsgBox ("You are logged in.")
End If
End If

End Sub

[June7]

You do not show declaring variable password. Unless it is declared as public in a module header, the password variable will always reset to empty string (or 0 depending on the type) when the procedure finishes execution. If you want to allow users to close form and return to the subform without having to repeat the password input, will have to either declare the variable as public/global or set value of unbound textbox on a form that never closes.

An issue with public variable is that it will be reset if code execution is interrupted. This can be an aggravation in debugging.

MsgBox has two forms - as a function and as a simple popup. Use of parens indicates the function form and it must be in an expression, like:

If MsgBox("Do you want to continue", vbYesNo) = vbYes Then
do something
Else
do this
End If

If you want just the popup form then:

MsgBox "Incorrect entry, try again"

Your code should error on the MsgBox lines.

[jhko]

Thanks so much June7. I did figure out the declaration part, tried to show the declaration in my sample code.

That is interesting about the two forms of MsgBox, I had not realized that. My code actually does work (at least in Access 2013), but I'll change it to be safe as per your suggestion.

Thanks again for the help.

[June7]

Correction to my earlier statement, I had it backwards. The popup form of MsgBox will error if used in an expression. What you had does work fine.