Using VBA r.sets to edit records in a table with values from unbound controls on form

[snipe]

Hey guys,



What is the correct for this:

Find a record in the table [Master] that with a date that matches a control [DelDate] and then push all of the values from a few unbound controls on the same from to the corresponding fields in the text. The unbound controls and fields are [D01], [D02], and [D03].

Never messed with the seek/findfirst functions in VBA, I only now how to add new records.

[pbaldy]

I wouldn't use either. Much more efficient to open the recordset on the desired record, like here:

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

[June7]

Why are you saving data from unbound controls? Is this calculated data? Saving calculated data often a bad idea.

Recommend naming controls different from the fields.

If the form is bound and the fields are included in the form RecordSource and form is on the relevant record, no need to open a recordset.

Me!DO1 = Me.tbxDO1
Me!DO2 = Me.tbxDO2
Me!DO3 = Me.tbxDO3

If not a bound form, can run an UPDATE SQL (examples for text, date, number fields).

CurrentDb.Execute "UPDATE tablename SET DO1='" & Me.tbxDO1 & "', DO2=#" & Me.tbxDO2 & "#, DO3=" & Me.tbxDO3 & " WHERE datefield=#" & Me.DelDate & "#"

[snipe]

This form is an entry form for our accounting department. Our system lets us run a report that lets us know our written sales and our delivered sales for a certain time frame. For the report that I am generating, I don't need to distinguish between the two, I just need them added together. The unbound control on the form adds them, and a macro sets the value of the total field. The form, that I didn't create, is pictured. I am just trying to make it more efficient.

Volume and Delivery is typed in, the unbound and hidden control adds them, and as of now, there is a macro that fires On Exit of each Delivered field that does a SetValue, making the Totals field equal to the appropriate unbound control. This works just fine as long as the user uses tab to cycle through. If he clicks into another field, the macro won't fire. I figured just removing that macro and the field that it set the value too, and just updating the table straight from the calculated fields would be more idiot-proof.

The form is bound, so I guess I could do what June mentioned. The "Me!D01" is the field in the table correct?

Do you have a better way to accomplish what I am trying to do?

[snipe]

Actually, June, that form is bound to a query linking two tables. The Volume and Delivery fields get put in one table, and the summed total gets put in the second. Would your method still work with some changes? What would I need to change?

[June7]

It might work if tables have 1-to-1 relationship but that is unconventional.

Why are totals in another table? Why save the Totals value at all? The sum of Volume and Delivery can always be calculated when needed. Saving calculated data is usually a bad idea. It really seems unnecessary in this case.

This looks like a non-normalized data structure.

[snipe]

I didn't build this database. I don't have the time to go through and change the whole thing up. Currently, I am just trying to make it idiot proof. No matter what the user does, I need the data to get entered in the tables. For now, until I have more time, I have gone ahead and used VBA to do a recordset edit and the selected date. This VBA is triggered upon exit of the form. Works great for now.

But yes, I agree, there is a ton wrong with the layout of the database. It's been in use for like 5 years, and I just don't have the time to mess with the structure.

[June7]

Another alternative doesn't involve opening recordset object - example for text, date, number field types:

CurrentDb.Execute "INSERT INTO tablename(field1, field2, field3) VALUES('" & Me.textbox1 & "', #" & Me.textbox2 & "#, " & Me.textbox3)

[snipe]

Ah I like that! But I don't think it would work for this project. I'm assuming that adds a new record. The table that these values are going to already has date value up to 2017.. Don't ask me why they did it that way.

[June7]

I showed UPDATE using Execute method in post 3.

[snipe]

Gotcha. Appreciate the help. Will definitely be using those on future projects.