Intersection between fields and records in table

[pedie]

Hi, how can i make the vba update x value where fields and records intersect to given x critiries in a table?

When i click button on the form i want following condition to run into table1 and update records
If field "Names" = form1.combobox1.value and
if field "Dates" = form1.combobox2.value?
update form1.textbox1.value where names and dates value intersect.

Thanks for helping
Pedie

 

[xenou]

You would use a UPDATE query:

Dim s As String
s = ""
s = s & " UPDATE Table1 SET "
s = s & " Field1 = '" & Me.txtName.Value & "',"
s = s & " Field2 = " & "#" & Me.txtDate.Value & "#"
s = s & ";"

On Error Resume Next
CurrentDb.Execute s, dbFailOnError
If Err Then
    Msgbox "Error updating table!"
Else
    Msgbox "Table Updated!"
End If

Note that we are careful when creating SQL statements to put text values in single quotes and dates in hashes (this is peculiar to Access - most DB's use single quotes for dates too).

Now Pedie, this is a basic SQL query ... I hope you working on learning your SQL...

The main thing about SQL is its set-oriented and not row/column oriented. It won't look for an intersection in a physical sense but in the mathematical sense of set operations - the set of all records in Table1 having this name in Field1 and this date in Field2. So this might be 0, 1, or many records that are updated as a result of this query.

 

[pedie]

Xen, thanks alot for helping out!
Apologies got late reply and 'll have to try this out tomorrow and get back again....thanks again and have a great! weekends!