Form Design Set-up

[Juan4412]

Hello - i have a form where I want the userID, FirstName, and LastName to be bound to fields in one table. Then I have 4 fields that I want bound to fields in a seperate table, such as manager name, hire date, starting pay, hours But on the button press event I want all 7 fields to be saved into ONE table.

Meaning userID, FirstName, LastName should be bound to tblUserInfo
And manager_name, hire date, starting pay, hours should be bound to tblManagerData

Then on the button press all fields should be submitted into tblManagerData

How would I set this up? I've bound to one table before but never multipe.

EDIT ---
Upon further thought on this, it might even be better to have a combobox with the concatenated values for FirstName + ' ' + LastName to allow the user to select the user they want to pull up the data for. Then on the selected index change populate userId, FirstName, LastName...then allow the user to input the remaining data and save to the tblManagerData.

[pbaldy]

I suspect you don't want the user info bound to the user table, more like you want to get values from that table to save into the manager data table. See if this helps:

http://www.baldyweb.com/Autofill.htm

As noted in the link, you would normally not save the names to the manager data table, just the user ID.

[Juan4412]

Nice - that shows how to display the data

On a button press event - how do I determine if I need to insert the data or edit the data?

For example, I found this that will allow me to insert data into the database from the form but how do I verify that I am not inserting when I should be editing?

Private Sub btnSaveToDatabase_Click()
 Dim rs As Recordset
 Set rs = CurrentDb.OpenRecordset("tblUserInfo")


 rs.AddNew
 rs!UserID = Me.UserID
 rs!Birthday = Me.Birthday
'more fields...
'more fields...
'more fields...
 rs.Update
End Sub

[pbaldy]

Well, most of us would suggest you use a bound form. That's code for an unbound form, which will be more work. If you want to go down that road, you could do this:

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblUserInfo WHERE UserID = " & Me.UserID)

If rs.EOF Then  'the user doesn't exist  
  rs.AddNew
  rs!UserID = Me.UserID
Else
  rs.Edit
End If

'rest of the fields
rs.Update

[Juan4412]

Do i need to map all the fields with rs.Edit or does it know what to do?

[pbaldy]

You still need to handle each field. Edit is just telling it you're going to edit a record rather than add one.

[Juan4412]

You still need to handle each field. Edit is just telling it you're going to edit a record rather than add one.

Okay, I am missing something here...I think I need to somehow bind the data entry fields
manager name, hire date, starting pay, hours

to the selected userID

For example, the entry into the table works exactly as expected, however when the form loads, the values are retained for all records on the form, NOT just the ONE userID that we input the values for.

[mike60smart]

Hi

I would recommend that you Bind all fields on the Form to a table.

You can have a Combobox for selecting a User and Store that usersID

The same would apply to a Combobox to select a Manager.

What is the purpose of the table where this data is to be stored? It sounds a lot like details of new Employees being added.

[Juan4412]

currently nothing is bound with the exception of a combo box where the user selects. Then i set the userID, FirstName, and LastName based off the selection of the combo box by using the link provided in post # 2 .

I *think* i have manager name, hire date, starting pay, hours bound to my table (when I design view the form it shows the control source is the field in the table), but the issue is that only those are bound so there is no id/pk linking them to the appropriate value.

[mike60smart]

Hi Can you upload a zipped copy of the database?

[Juan4412]

I can't get it to add to this site...when i click add file then upload, it shows me the % timer...and it gets all the way to 100% but then the file never appears?

[mike60smart]

Hi

The database you have uploaded to Filedropper bears no resemblance to the database you have described to everyone??

[Juan4412]

it's different field names (working on two projects at once) but same concept i am trying to achieve.

[mike60smart]

Sorry but I will leave this because I am not understanding what exactly you are trying to achieve.

Luck with your project

[Juan4412]

Sorry but I will leave this because I am not understanding what exactly you are trying to achieve.

Luck with your project

I went back to the project i was referencing above...does this help clarify since the field names are the same?



http://www.filedropper.com/test_19271