Fill form from another form

[cochese]

I have found similar issues, but I'm either daft or so green that I can't relate the solutions to my problem. Sorry.

I have a form with a subform. The form is designed to search for an employee and then display a few facts about the employee in the subform (e.g. first and last name, hire date, photo, etc). This part works.

Once the user finds the correct employee, they will then press a button to create a new incident record (in a table different from the employee table). This button will launch a new form that needs to have the data from form #1 (first and last name, hire date, etc) prefilled in form #2, and then the user will enter new data into other text boxes in form #2.

Example:

[FORM 1 : SUBFORM]
Last Name [textbox]
First Name [textbox]
Hire Date [textbox]
Photo [image of employee]

[FORM 2 : SUBFORM]
Last Name [populated from form 1]
First Name [populated from form 1]
and then there will be other text boxes that are filled out by the user and saved as a new incident record, which includes a list box filled from a table separate of the employee table.

One other thing is, form #1 should close automatically once form #2 is launched.

One thing that is shocking to me about Access is that every solution seems to require VBA code. Now I understand VBA but I'm shocked that in order to do what I feel are basic things VBA has to be written every time. Is there a non VBA way to do this?

Thank you!

 

[boblarson]

First up - I think we need to explain that you do NOT need to put all of that first form data in the second form, at least you don't need to store it in the table which is separate from the employee table. The ONLY thing from the employee table you should be storing in that second table is the EmployeeID. That is it. No more. Storing data more than once is a relational database no-no, with a few rare exceptions. So, you would only store the ID (if you don't have one then you should make one - an autonumber would do) and then you store that number in the second table in the same field name - EmployeeID but the data type is Long Integer and that is considered to be the Foreign Key because it isn't a key from that second table it is from the first.

One of the mistakes new Access users make is assuming that Forms and Reports need to have a table as the record source. But they can use QUERIES too. You can use a query in 99% of the places you would use a table. So you use a query which would include both tables and linked by EmployeeID and then you can use the Employee Name from the Employees table but still have the information from the second.

 

[cochese]

Bob:

I'm actually not that green.

I am using only the employee id in the actual record of the incident table. I want to display the name because I know my users and if they don't see the name there they will be confused.

Whether a name is displayed though, once I add a new record to the incident table (from form #2) it will need to include the employee id from form #1 and I'm not sure how to relay a piece of data from form #1 to form #2 that will then be put in as a new record in another table from form #2.

 

[boblarson]

I am using only the employee id in the actual record of the incident table. I want to display the name because I know my users and if they don't see the name there they will be confused.

Another piece of learning for you - users should not be in the tables or queries directly. You give them interfaces (forms) for managing records and such. So, in your form you can display the name even though the ID is stored. You can use a combo box for that. Now, just make sure you don't use lookups at table level (they can be a temptation) because they will only cause you pain. See here for why.

Whether a name is displayed though, once I add a new record to the incident table (from form #2) it will need to include the employee id from form #1 and I'm not sure how to relay a piece of data from form #1 to form #2 that will then be put in as a new record in another table from form #2.

You can get the ID from the first form by passing the ID as OpenArgs. That is a special thing you can use which Access then knows to keep that information with the form being opened.

You first use this code to open the second form:

DoCmd.OpenForm "YourSecondFormNameHere", DataMode:= acAdd, OpenArgs:= Me!EmployeeID

And then your form would be opened with that and the OpenArgs then includes the EmployeeID from the first form. So then in the On LOAD event of the SECOND form we need to use the OpenArgs:

If Me.OpenArgs <> vbNullString Then
   Me!EmployeeID = CLng(Me.OpenArgs)
End If

We have to convert to the Long Integer (which should be the EmployeeID field datatype in the second table) so it will not get a type mismatch because OpenArgs gets passed as a STRING.

And that's about it. I'm sure you'll have other questions about this and all but this should get you going.

 

[cochese]

Thanks again. I meant to write not that green actually (I made the edit now). I understand normalization (although not near mastering it) and I use forms instead of having the user ever directly deal with queries or datasheets.

Your solution requires code, and while I can work with that, I'm dumbfounded that it's near impossible to do anything in access without putting code in. I will try your suggestion today, and let you know how it goes. Thank you again.

 

[boblarson]

I'm dumbfounded that it's near impossible to do anything in access without putting code in. I will try your suggestion today, and let you know how it goes. Thank you again.

Actually you can use macros but they are severely more limiting than VBA. I think in VBA terms now (it wasn't always the case) and have since 2000 when I finally took the plunge. And it has made a world of difference in what I can do.

 

[cochese]

Before I tried your suggestion I found something else that appears to have worked, but I want to ask you if you see any potential flaw in it.

For form #2's on load event I put in:

Me.EmployeeID = [Forms]![frmMgrSearch_M]![frmMgrSearch_S]![tbxID]

Which populate's the employee textbox on form #2, and when I add the record it works. Form #1 is still open when form #2 loads however (I would prefer form #1 to close but I'm not sure if this is possible with how I accomplished it above).

?

 

[boblarson]

That is okay, provided

1. that the frmMgrSearch_S form remains open.

2. that you check to see if it is a new record or else you run the risk of overwriting existing data. So a quick

If Me.NewRecord Then
   Me.EmployeeID = [Forms]![frmMgrSearch_M]![frmMgrSearch_S]![tbxID]
End If

should work.

 

[cochese]

I made the form data entry only so it defaults as a new record, correct?

 

[gemma-the-husky]

designing an interface is a complicated thing

access can be used with minimal code - but you need to appreciate that a great deal of an application code is designed to prevent users entering rubbish - which needs code.

so in your case you have a form, and the user sees the active employee. So he now clicks an button which opens a form, to enable him to add an incident relating to that employee

now it ought to be clear to the user that the incident relates to the selected employee - but if you think it isn't then, yes it will need some attention to show some of the data from the employee form on the incident form.

alternatively you could show the incidents relating to the employee in a subform - and then you probably wouldn't need any code at all - but you would need a good understanding of how forms/subforms work.

But good applications DO need code - hundreds or thousands of lines of code.


--------
think of access as a complex spreadsheet - where you are trying to limit carefully what users can enter in any given cell - how would you expect to do that without some code?

 

[cochese]

I built a lot of financial tools in Excel so I'm pretty good with VBA, and I'm not intimidated by it, but I am shocked how difficult it is to do fairly simple (in my opinion) things without code in Access.

I understand coding allows for a lot of added quality, but it's amazing that it's required just to do simple requests.

 

[boblarson]

I understand coding allows for a lot of added quality, but it's amazing that it's required just to do simple requests.

It depends on what you're doing. Macros can do quite a lot, if you prefer them, but a good majority of those of us who hang out here prefer using VBA over macros as it does provide many more options.

So what do you consider "simple requests?" What should Access be able to do without code? Given that information, we might be able to tell you more about codeless solutions.

 

[LewisWA02]

Can this be modified to fill multiple fields?
Basically, I'm wanting to auto populate a technician's name and the department he's in when the form opens. Right now I can do one or the other, but I'm not sure of the coding necessary to do both. Any help is appreciated. Thanks!

 

[boblarson]

Can this be modified to fill multiple fields?
Basically, I'm wanting to auto populate a technician's name and the department he's in when the form opens. Right now I can do one or the other, but I'm not sure of the coding necessary to do both. Any help is appreciated. Thanks!

How are you currently doing it? What code are you using? What is your table structure like (in other words how is that data stored for associating the department with the technician)?

 

[LewisWA02]

I'm currently using the code you listed earlier in the thread:
Code:
DoCmd.OpenForm "YourSecondFormNameHere", DataMode:= acAdd, OpenArgs:= Me!EmployeeID
Code:
If Me.OpenArgs <> vbNullString Then Me!EmployeeID = CLng(Me.OpenArgs)End If
I've modified it as such:
First form:

Dim strTech As String
Dim strDpt As String
strTech = Me!Technician_Name
strDpt = Me!Department
DoCmd.OpenForm "Assignment Details", DataMode:=acFormAdd, OpenArgs:=strTech

second form:

If Me.OpenArgs <> vbNullString Then
Me!cboTechA = Me.OpenArgs
Me!txtPlantDepartment = Me.OpenArgs


As for tables:
tblAssignments has a list of all assignements broken down by technician and department

tblSB3 is a comprehensive table with all the requirements stored for that particular tech.

The information is filtered based on what department the techinican is in.

 

[LewisWA02]

Thanks for the time, but I think this will work:

On click event of first form:

DoCmd.OpenForm "Assignment Details", acNormal, , , acFormAdd

On open event of second form:

Dim strTech As String
Dim strDpt As String
strTech = Forms!frmSB3Details!Technician_Name
strDpt = Forms!frmSB3Details!Department
Me!cboTechA = strTech
Me!PlantDepartment = strDpt

Billy

 

[boblarson]

Now you do know (I hope) that you shouldn't be storing the Technician name or the department twice. You should be storing the ID of the technician record in the second table but only DISPLAY the name and department in the second form. You want to store the ID but not the name because if you had a typo, for example, you can fix the data for all of it in ONE place. And the Department, if it is assigned in one table is easy to get using a query and including that table in the query. So you don't store it again as that would violate the "rules of normalization."

 

[LewisWA02]

Yeah, I do know that (now). This was one of the first databases that I'd built and I didn't follow very good conventions then. It originally only consisted of the tblAssignments, a supervisor table, and a tech table. That worked ok for a while, but then the rest of the database was built around those first 3 tables. The Assignment table is really only a very small part of the rest of the db. Everything else built so quickly around it that I didn't think to change anything. I'm going through and trying to clean everything up, but the DB is in use daily so it's kinda slow going. Thanks for your help!

Billy