Insert/Update 1

[takeover]

Hi,

I have a form with unbound text boxes. While the form loads, these text boxes are populated from the database. When I click the save button after making updates or
inserts to these text boxes, I need to selectively update or insert the data to the database (like if the data is existing in the db correspondingly, update the data
and if it doesn't insert it). Is there any way I could do it?

takeover

 

[willir]

TakeOver

There are several approaches. Perhaps the easiest is to use DLookup or DCount...
[tt]
Dim strSQL as String

If (DLookup("[YourField]", "YourTable", "[PrimaryKey] = " & Me.TextBoxKey)) Then
strSQL = "APPEND ..."
Else
strSQL = "INSERT ..."
End If
[/tt]

(DLook( ... )) returns a true if a record is found or false if nothing is found.

You can also use ADO or DAO and .Recordcount or .EOF to test if a the record exists.

Richard

 

[takeover]

Hi,

My primary key is a key with three columns. How could I code this?

takeover

 

[willir]

Something like...

Dim strWhere as String, strQ as String
'use mix of string, date, numeric, numeric
'will keep field names simple since no info provided

strQ = Chr$(34)   'used for double quote

strWhere = "txtField1 = " & strQ & Me.txtBox1 & strQ & " AND dateFeidl2 = #" _
& Me.txtBox2 & "# AND numberID3 = " & Me.txtBox3 & " AND numberID4 = " & Me.txtBox4

DLookup("[YourField]", "YourTable", strWhere)

Explaination:
- Text fields variables have to be encapsulated with quotes. This is complicated by the fact that you also need to encapsulate the WHERE string with quotes. I use a varaible, Chr$(34) -> strQ. Others will use """ (three double quotes - a quoted quote), or "'" (single quote encaspulated with double quotes)
- Date field variables have to be encapsulated with octophorp or pund or number sign - #
- Me.txtBox refers to a textbox control on the local form.
- For long text strings, you can continue typing OR wrap text. The underscore tells Access to continue onto the subsequent line of code as part of the current line

Richard

 

[takeover]

Hi Richard,

That was quick. Thanks a lot!

takeover