Need help please (1 Viewer)

Alvin85 · Jul 24, 2018

I have a table namely tempDB. The table have col namely [PriceOfToy],[dateOfPurchase]. User will key in the price of toy and date of purchase.

How do I retrieve the all datas from tempDB and check if there is a same dateOfPurchase. If there is a same dateOfPurchase, then the PriceOfToy will be added together. The new value of PriceOfToy will then be updated in the previous row instead of creating a new row.

Pat Hartman · Jul 24, 2018

Wouldn't you want a quantity column? That way you would update the quantify. To get the total, multiply the price by the quantity. If the prices of the two units are different, wouldn't it make more sense to have two rows?

Is this a school project. I feel like I answered this question some time ago. The person was working on a POS system. The ultimate solution was to do the data entry into unbound controls. Then in your save button, look to see if the Item exists for that date. If it does, you run an update query otherwise you run an append query. I also don't think that those two fields are sufficient. Wouldn't you also need an item number? Take a look at a sales receipt to see what type of information it contains.

Alvin85 · Jul 24, 2018

Hi Mr pat,
As the value of the toy might change, i will only need to add the newly keyed price by the user with the existing price stored in the database.

dpspng · Aug 18, 2018

What criteria should be given to get the records in a query where if field A=1 to get last month records and if Field A is not equal to 1 then last 6th month records?

arnelgp · Aug 18, 2018

use a Form for your Table as Data Entry.
add a code to this Form's BeforeUpdate Event:

Code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim thisPrice As Double
    If Me.NewRecord Then
        thisPrice = Me.PriceOfToy
        With Me.RecordsetClone
            If Not (.BOF And .EOF) Then
                .FindFirst "[DateOfPurchase] = #" & Format(Me.DateOfPurchase, "mm/dd/yyyy") & "#"
                If Not .NoMatch Then
                    Me.Undo
                    Cancel = True
                    Me.Bookmark = .Bookmark
                    Me.PriceOfToy = thisPrice
                End If
            End If
        End With
    End If
End Sub

it will not add new record if there is
same date on the table.
it will replace the PriceOfToy, but you
need to press Enter key for the record
to be saved.

arnelgp · Aug 18, 2018

for dpspng:

are you using form? this sample will use Parameter, [A] Integer. note aircode:

Code:

select 
      table1.* 
from 
      table1 
Where 
             iif([A]=1, 
             Format([dateField],"yyyymm") = Format( DateAdd("m", -1, Date), "yyyymm"), 
             Format([dateField],"yyyymm") >= Format( DateAdd( "m", -6, Date), "yyyymm"));

Alvin85 · Aug 19, 2018

Hi arnelgp,

May i know what is what is .BOF and .EOF from your coding?

Private Sub Form_BeforeUpdate(Cancel As Integer) Dim thisPrice As Double If Me.NewRecord Then thisPrice = Me.PriceOfToy With Me.RecordsetClone If Not (.BOF And .EOF) Then .FindFirst "[DateOfPurchase] = #" & Format(Me.DateOfPurchase, "mm/dd/yyyy") & "#" If Not .NoMatch Then Me.Undo Cancel = True Me.Bookmark = .Bookmark Me.PriceOfToy = thisPrice End If End If End With End If End Sub

arnelgp · Aug 19, 2018

Bof, beginning of file, eof, end of file. If both are true then there us no record.

Need help please (1 Viewer)

Alvin85

Registered User.

Pat Hartman

Super Moderator

Alvin85

Registered User.

dpspng

New member

arnelgp

..forever waiting... waiting for jellybean!

arnelgp

..forever waiting... waiting for jellybean!

Alvin85

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread