Return to last created record after On_Click

[Marinus]

Hi Guys,

Trying to requery my form and come back on the last created record, however I get a data type mismatch in criteria, run-time error 3464

Pretty new to this so I hope someone might help..

The code;

Private Sub List203_Click()

DoCmd.RunCommand acCmdSaveRecord

Dim returnID
returnID = Me!ID
Me.Requery
Me.Recordset.FindFirst "ID = '" & returnID & "'"



End Sub

 

[MarkK]

What data type is ID? You are treating it like a string when you use the single quotes ...

[COLOR="Green"]'do this if ID is a string[/COLOR]
Me.Recordset.FindFirst "ID = '" & returnID & "'"
[COLOR="Green"]'otherwise you want this if ID is numeric ...[/COLOR]
Me.Recordset.FindFirst "ID = " & returnID

 

[pbaldy]

If it's a numeric field:

Me.Recordset.FindFirst "ID = " & returnID

You may find this helpful:

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

 

[Marinus]

What data type is ID? You are treating it like a string when you use the single quotes ...

[COLOR="Green"]'do this if ID is a string[/COLOR]
Me.Recordset.FindFirst "ID = '" & returnID & "'"
[COLOR="Green"]'otherwise you want this if ID is numeric ...[/COLOR]
Me.Recordset.FindFirst "ID = " & returnID

Thanks Lagbolt, that is a clear explanation, sure goes in my library..

I have one more favour to ask that I have been struggling with, I had help, but still wasn't able to make it workable.

I have a SQL update query that I need to run automatically every time before the last record is saved. I can sent my app you would certainly make my day if you could find a solution as I am on it for days now.

Would you mind??

 

[Marinus]

If it's a numeric field:

Me.Recordset.FindFirst "ID = " & returnID

You may find this helpful:

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

Thank you very much, I will have a look at your website, I am sure there is lots to learn for me and believe me I really need it.. Just writing my first app and have another problem as described to Lagbolt, Any help is greatly appreciated in getting the touchscreen form up to scratch..

With this I also thank everyone for their help, could have come this far without guys like you all on this forum which I find the best of all..

 

[MarkK]

Without looking at your app it seems suspicious that you need to update data in response to data being updated. This seems very likely to be symptomatic of a design dependency that you want to avoid. Stored data should be raw. If a consumer of that data needs to do a calculation or have logic applied to a result set, then that should occur at retrieval time, not at storage time.

 

[Marinus]

Without looking at your app it seems suspicious that you need to update data in response to data being updated. This seems very likely to be symptomatic of a design dependency that you want to avoid. Stored data should be raw. If a consumer of that data needs to do a calculation or have logic applied to a result set, then that should occur at retrieval time, not at storage time.

I do appreciate your answer, however with my knowledge I had no other choice then to do it like this and make it transparent. The situation is that I have one main form handling the intake of Waste, On this form people choose a material from a database that contains price information. Buying an Sales price, however these prices change and I need to keep the old prices to calculate stock value and for reports, average sales price etc.. Lack of knowledge made me do it like this as I thought that this way the latest prices were inserted in the corresponding fields.. My friend.. You really make me think.. Second reason for choosing this methode is that once the price is entered, only a deciding salesman can enter it.. But then again.. As in my signature .. very Very Novice

Does this mean I can call the prices from my material table by clicking a value in a listbox??

 

[MarkK]

It makes sense to me that if you add a record then you might look up other values in the database and save those values with the record you're adding. In a payroll system, for instance, it's likely you'd save the rate-of-pay for an employee along with the hours for a certain pay period since you don't want future pay rate changes to alter historical payroll calculations. In this sense duplicating the pay-rate for every pay period is essential.
But you've posted your database. Where is the problem in there? Please describe exactly how to cause the problem, which form to open, and what button to click, and I'll take a further look.
Cheers,

 

[Marinus]

It makes sense to me that if you add a record then you might look up other values in the database and save those values with the record you're adding. In a payroll system, for instance, it's likely you'd save the rate-of-pay for an employee along with the hours for a certain pay period since you don't want future pay rate changes to alter historical payroll calculations. In this sense duplicating the pay-rate for every pay period is essential.
But you've posted your database. Where is the problem in there? Please describe exactly how to cause the problem, which form to open, and what button to click, and I'll take a further look.
Cheers,

The people will work on the form Touchscreen, they will create a new docket, input a name, pick a material, a haulier. Then click on Docket to generate a docket_number. A loaded truck will drive on a weighbridge and the weight will be added by means of DDE, The sales person is clicked and the record should be stored. Then a the empty truck will return, the right docket will be selected from the list box Docket Number and the empty weight is entered trough DDE. Then a docket is printed and the driver walks over the road., the girls prints a payslip and the docket is marked paid and will drop off the docket number listbox. Would love to lose the Command Button PriceUpdater, as this only works when a docket number is selected.. Thanks for the offer Lagbolt... It would be a big step for me

 

[MarkK]

No, I mean update that needs to occur when a record is saved. This is what we're talking about right? Where does that happen? What step is that?
Cheers,

 

[Marinus]

No, I mean update that needs to occur when a record is saved. This is what we're talking about right? Where does that happen? What step is that?
Cheers,

Sorry for misunderstanding, when a salesrep is selected, the fields Docket!Buyprice and Docket!SalePrice need to be updated with the fields from the material table, then the record should be saved and added to the Name Docketnumber listbox..

I have been playing and found this to work;

Private Sub List203_Click()

DoCmd.RunCommand acCmdSaveRecord

Dim returnID
returnID = Me!ID
   Me.Requery
   Me.Recordset.FindFirst "ID = " & returnID

DoCmd.RunCommand acCmdRefreshPage

How hard I try, I can not get the Update_Query QRY_GetMaterial_Price to run with a save/refresh afterwards so it also the last record could be used without running the query via a command button..

Now I first have to select a record from the Docket_Number list, then go back to the last record and run the cbPriceUpdater..

 

[MarkK]

OK, so on the touchscreen form, which represents a single Docket record, you have a buy price and a sell price. That data belongs to the docket, not the material. It seems to me that you might produce a new docket tomorrow and the buy and sell prices for Copper, say, will change. (Where do those prices actually come from?) Yesterday's buy and sell prices, if they were stored in the materials table, are meaningless.
See what I'm getting at?

 

[Marinus]

OK, so on the touchscreen form, which represents a single Docket record, you have a buy price and a sell price. That data belongs to the docket, not the material. It seems to me that you might produce a new docket tomorrow and the buy and sell prices for Copper, say, will change. (Where do those prices actually come from?) Yesterday's buy and sell prices, if they were stored in the materials table, are meaningless.
See what I'm getting at?

Today's prices come from the Materials table, and I see where you come from, reports are going to be based on history and historic prices that need to be stored in the Docket table not the Material table, sorry if I did not make that clear in previous posts....

Sorry for misunderstanding, when a salesrep is selected, the fields Docket!Buyprice and Docket!SalePrice need to be updated with the "data" from the material table, then the record should be saved and added to the Name Docketnumber listbox..

Thanks for thinking with me...

 

[MarkK]

But then you don't need an update query. The buy and sell prices are in the record currently being editted, so in the Before_Insert event you can look at the materials table, scoop that data and copy it to the current record.

Private sub Form_BeforeInsert(Cancel as integer)
[COLOR="Green"]'  Gets pricing data from the Materials table to store with the current record[/COLOR]
  with currentdb.openrecordset( _
    "SELECT BuyPrice, SellPrice " & _
    "FROM Materials " & _
    "WHERE Material = '" & me.Material & "'")

    me.buyprice = !BuyPrice
    me.sellprice = !SellPrice
    .close
  end with
end sub

Maybe you need to change some names and so on.
Cheers,

 

[Marinus]

But then you don't need an update query. The buy and sell prices are in the record currently being editted, so in the Before_Insert event you can look at the materials table, scoop that data and copy it to the current record.

Private sub Form_BeforeInsert(Cancel as integer)
[COLOR="Green"]'  Gets pricing data from the Materials table to store with the current record[/COLOR]
  with currentdb.openrecordset( _
    "SELECT BuyPrice, SellPrice " & _
    "FROM Materials " & _
    "WHERE Material = '" & me.Material & "'")

    me.buyprice = !BuyPrice
    me.sellprice = !SellPrice
    .close
  end with
end sub

Maybe you need to change some names and so on.
Cheers,

Get the drift, did the change and didn't work, but this looks much better to me then the query, will play with this for a while and let them work with a button for the next few days to let me do so.. Great LagBolt..

Private Sub List203_AfterUpdate()

'  Gets pricing data from the Materials table to store with the current record
  With CurrentDb.OpenRecordset( _
    "SELECT BuyPrice, SalePrice " & _
    "FROM Materials " & _
    "WHERE Material = '" & Me.Material & "'")

    Me.BuyPrice = !BuyPrice
    Me.SalePrice = !SalePrice
    .Close
  End With
End Sub

Will let you know how I get on or if I have any question..

P.S. Did the Private sub Form_BeforeInsert(Cancel as integer) first, that didn't work..

 

[MarkK]

Didn't work? That's not enough information.