opening a form from another form based on textbox value (1 Viewer)

[anto.danny]

hello

I have two tables "Employee" & "Jobsheet" which has a common column called "EmployeeID" which is unique in table Employee and has duplicates in table Jobsheet.

I've also made two forms Employee and Jobsheet based on the two tables. When I click on the "EmployeeID" textbox in Jobsheet form I want the employee form to open filtered to the record of the same "EmployeeID".

I have done this on a form and report of the same table. applying the same to two forms doesn't work.

 

[Pyro]

The following should work:

DoCmd.OpenForm "<Employee Form>" , , , "EmployeeID = " & Me!EmployeeID

Replace <Employee Form> with the name of your employee form.

Note that you may need to save the record first if it is a new record that has not yet been committed. Alternatively, you could reference the textbox control which has your EmployeeID in it instead of the field itself.

 

[JHB]

Else show the code you've in the "EmployeeID" textbox control.

 

[anto.danny]

I made the text box as hyperlink and pasted the code in onclick event. When I click on employee ID, Im asked to enter parameter, so type the employee number and then employee form opens with nothing on it.

 

[Pyro]

Attached is a real quick example I put together for you.

I'm not sure what version of Access you're running, so i saved it in 2000.

 

[anto.danny]

The employee ID is not number its alphanumeric in my case. I guess I did some thing wrong will report again after a few trials...:banghead::banghead::banghead::banghead::banghead::banghead:

 

[anto.danny]

I made two files one with employeeid as a normal number and the other one with alphanumeric one. The one with number work and alphanumeric one says enter parameter and I have to enter the ID again.

 

[anto.danny]

I came up with a idea..i made a query that does search based on the value on textbox and opens the form with filtered view, I added the query to record source of employee form and added a button on jobsheet to open the employee form. Its a mess but it gets the job done

 

[Pyro]

If your field is alphanumeric, then in the code as per my example, just replace this line:

DoCmd.OpenForm "frm_Employee", , , "EmployeeID = " & Me!EmployeeID

with:

DoCmd.OpenForm "frm_Employee", , , "EmployeeID = '" & Me!EmployeeID & "'"

Note the extra single quotes.

 

[anto.danny]

yup that did the trick..thanks mate!