update record based on two fields

[sohailcdc]

Hi there
I am newbie in access and trying to learn, based on different Thread I am able to learn how to Add/Update record including existence of record based on single unique field

Now I trying to learn how to check existence of record on multiple field before adding (at least two) and how to update the record with multiple field record

Below is my testing table fields

Year Month Working Days
2013 Jan 20
2013 Feb 17
2013 Mar 22

Now two situations adding a new record and updating the existing record
Which means the unique fields are (Year + Month)

Any help would be appreciate and thanks in advance

 

[Mihail]

Something like this (see attachment).

 

[sohailcdc]

Hi Mihail, thanks
I can understand update is working, but adding it creating duplicate record

 

[Mihail]

This is another story.
You are on your own with this.
You must check this BEFORE in order to NOT create duplicates.

 

[sohailcdc]

How to check duplicate record before saving based on two variable

 

[Mihail]

Using a SELECT query with parameters (the new values that to wish to be written to the table).
And with a DCount function to count the number of returned records from this query. If there are more than ZERO a duplicate will be written.

 

[missinglinq]

...Which means the unique fields are (Year + Month)...

You're speaking of checking whether or not a given combination of Month and Year already exists, and not allowing a New Record with the same combination? If so, you need to replace MonthField and YearField with their actual names. You also need to replace Me.MonthField and Me.YearField with their actual names of these Textboxes, on the Form, if they are different from the Field names:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 If DCount("*", "ActualTableName", "[MonthField] & [YearField]='" & Me.MonthField & Me.YearField & "'") > 0 Then
  MsgBox "This Combination of Month and Year Already Exists!"
  Cancel = True
  MonthField.SetFocus
 End If
End Sub

This assumes that MonthField and YearField are defined as Text Fields. Not really sure if you could do this with them as Date Fields. Also note that Month and Year are Reserved Words, in Access, and should not be used as Field Names.

...I trying to learn how to... update the record with multiple field record...

Sorry, I have no idea what the above statement means.

Linq ;0)>

 

[sohailcdc]

Thanks Missinglinq

I am using following update statement for editing/updating record with one unique variable field (however, my current table i don't have any unique field other than combination (Year & Month) field - hope i clear

now, question is in "where statement", how i can use (year & month) as combine variable


"Update tlbname set tlbfieldname='" & me.txtformfieldname & "'" & "where tlbfieldname='" & me.txtfieldname & "'"

 

[Mihail]

MISSINGLINQ show you a solution based on SQL strings because he know SQL.
His solution is smarter and faster than my one. I'm sure that he will show you how to manage things with regarding to yours new question.

On the other hand you can teach yourself (in fact Access will teach you) basics in SQL.
Try to design an update query (as usually, using the Query Design window).
After you will obtain an working one query (which do what is supposed to do), switch to SQL view. Here you will see how Access have constructed the SQL string and you can learn a lot from this. And... you will see how Access have deal with the Where clause. Many times you can even copy this SQL string and use it (maybe by doing very small changes) in your VBA code.