Update and add data to table

[dedjloco]

So you gave been great with the help so far.
Now I want to store the values I put in the form "Product Specs". I have a table "Product Specs" as well, where the data should be stored. If you look at this table then you see that its an exact match of all the data.
But what I want is that you can put a "project nr" in the matching field in the form. and then when it matches a record it should get all the values from that record and put it in the form. and when you change something it should change in that record as well. But it should change a existing record when you have put in data in the form and you want to change to an other "project nr".
And if you put in an "Project nr" that doesn't exist yet it should make a new record.

I hope this is understandable.

 

[The_Doc_Man]

Look up the "NotInList" event to see how to take action for combo boxes that allow free-form data input.

In essence, you create a combo box to select your project but there is an option to allow for a "NotInList" situation to occur; in other words, a write-in project. I think it is the "LimitToList" property, which for YOUR case would be set to False.

The NotInList event is where you would do your logic to decide to populate from an existing project or create a new project.

 

[dedjloco]

I tried your suggestion, but what I don't understand is how the other controls can be "coupled" to the value in the combo box.
So if I select a value from this box al the other value will change to what is stored in the record with this value. And when I change something it now just change to the top record and not in the record I select.

 

[The_Doc_Man]

The trick is that with "Not In List" you have a way to open a recordset or execute some SQL to add a new value to the source of your list, assuming it is record-based. So you would take the NotInList trap, do a couple of Input boxes to get what you need, and then either open a recordset to the box's .Recordsource or synthesize an INSERT INTO query string that adds the new value to the box's .Recordsource, then .Requery the box.

Then you can select the "write-in" value that you just added to the list, because now that new value will be there. Or at least that's the theory.

You seem to be confused about what is changing, and the way you stated your concern, I don't know if your problem description was clear. The original statement SEEMED to be a simple case of NotInList handling, but if there is more to it, you need to be explicit.

 

[dedjloco]

But what I want is that you can put a "project nr" in the matching field in the form. and then when it matches a record it should get all the values from that record and put it in the form. and when you change something it should change in that record as well. But it shouldn't change a existing record when you have put in data in the form and you want to change to an other "project nr".
And if you put in an "Project nr" that doesn't exist yet it should make a new record.

Tis is what I stated.
So I think what you discribed helped for 1 part of the problem, when you put in a new number, so thats good. But for the second part I want the values to change to existing Project number's as well.
So from the beginning.
1. you put in a new number you get a "clean" form.
2. you have an existing number all the changes that are stored in the database will be retrived from the table.

What I gor now is that you can create new numbers, but if you change it to an existing everything stays as it is at that moment and you overwrite all the prevouis saved data.

I hope it's a little more clear now?

[EDIT]
So got a lot of stuff working again. but still not succeeded in the secoond part. So easy question. I want all the checkboxes en textfields to change in the values stored in the table in the matching record, when you select a number in the combobox.

 

[The_Doc_Man]

Ah, wait... I think I see what you just asked. Let me see if I can tell you what I think you want to do.

You select a project. You can handle the case for a totally new project using the NotInList event, or at least that is what I got from your post. But... when it is NOT a new project, you want data from the project record to fill in this form of yours. However, the form is NOT bound to the project, it is bound to a product.

I don't know how you did this, but for the ComboBox to select the project, you can recover more than one field but just not display the "extra" data. That information would be available in other columns in the box. However, the box's recordset is not updateable as far as I recall.

What you appear to want is that if you make changes to items obtained from the project table before you save the product information, you want the project table, which is NOT bound to your product form, to update based on your edits.

If that is a correct reading of your intent, I'll offer a couple of thoughts.

Access does not allow double-binding in a single form. So if you save the record for your product, that is what is saved. If any of the fields on the form from the project lookup were modified, they are soft-bound, not hard-bound and therefore will not update.

To make this work, you would need to synthesize and .Execute an UPDATE string to update project data based on the values on your form at the same time that you are doing the update to the product table. Since the product form is NOT bound to the project table directly, you should not get into conflict trouble with a locked project table.

Or you could open a recordset, use the .FindFirst option to find the correct project record, do a .Edit on the recordset, change the values from the relevant form controls, do the .Update, close the recordset.

I would synthesize the UPDATE string myself, but Access allows multiple ways of doing some of these things.

 

[dedjloco]

Yes this is exactly what I mean. And I understand what you're pointing to. I could make code that would do this displaying and updating of all the data, one by one. But possibly there is an easy way with a query or something, but I'm not that advanced with access yet. I'm just pretty familiar with VBA.
So I'm looking for a way to get all the data at once from the recordset and update all the data as a whole.
And googling for the solution is very difficult when you don't know precisely where to look for.

[EDIT]
So I created a code that is working on itself, but when I try to make it "parameterizable" so I can call it multiple times it doesn't.
Here is the code:

Public Sub CurrentRecordMacro(MyObject As Object, TableField As Object)
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
   Set dbs = CurrentDb
   Set rst = dbs.OpenRecordset("tblProductSpecs")
   rst.MoveFirst
   Do Until rst.EOF
      If rst("Project Nr") = Me.Project_Text Then
         MyObject = rst!TableField
      End If
      rst.MoveNext
   Loop
End Sub

and the I call it like this:

CurrentRecordMacro Me.BH_Check, Product_BH

So I should be able to put every object from my form in there and the matching table field.
When I put Me.BH_Check and Product_BH in the code itself it works. So probably something is wrong with the data types of the user defined variables. But I don't know what it should be then?