[RESOLVED] ReQuery a form

[kfcSmitty]

How would one go about doing this?

My problem is -- I have a form, and when I edit the data, then click the save button, the table is update...However, I have to close the form and reopen it for the changes to appear.

I have tried RecordSet.ReQuery, but it gives me an error on some of my other code, stating that the value isn't valid.

I was thinking of just editing the recordsource and updating, but I don't know how to make this code work in a SQL statement in VB code :/

SELECT DCount("ID","WorkerInformation","ID<'" & [ID] & "'")+1 AS SeqNr, * FROM WorkerInformation;

As the double quotes raise an error...SO I tried it with single quotes, but that didn't work either...

I'm gonna keep plugging away at this, but if anyone can help me Id really appreciate it


And I have a sinking suspicion I am going to owe Danny another rep

[dannymking]

Errm Yup..

Hello again..

Dim cr As String
cr = Chr(34)
strsql = "SELECT DCount(" & cr & "ID" & cr & "," & cr & "WorkerInformation" & _
 cr & "," & cr & "ID<'" & cr & " & [ID] & " & cr & "'" & cr & ")+1 AS SeqNr, * FROM WorkerInformation;"

That should do it... test it with a message box to see if it replicates what is in the recordsource or controlsource

[Ecniv]

here
I think it may be jro (jet something objects) which has a refreshcache property. Have a look and see whether it clears the problem.

[dannymking]

But then Again..

Me.Requery

Should do the job.. not

Me.Recordset.Requery

[kfcSmitty]

my access doesn't seem to support JRO, I cant even declare a new object...

And Danny, although that was the right SQL, that loaded me to the first record everytiem I ran it..so its no good



is there a way to requery it without it loading to the first record again?


and I tried Me.RecordSet.ReQuery and just Recordset.ReQuery and Me.ReQuery...they all raised the error

"The value you entered isnt valid for this field"


Although It is inputting a name "Travis Smith" into a textbox....

[dannymking]

If this is the code I did yesterday.. then you would just need to call the combobox change event with the lngCurrRec variable as this should still reflect the last chosen record number..

[kfcSmitty]

yeah..problem is, it calls the form_current before it does the click event...Oh well..Guess I will keep working on it

[dannymking]

What are you doing in the form current as the error sounds like it is hitting here

[kfcSmitty]

form_current is where i populate all the fields on the form, as well as call some other procedures for calculations

If I can get CurrentNr to change before form_current is called, then it should work fine

[kfcSmitty]

when i change the recordsource it calls the form_current...found that out

So now I tried just calling form_current at the end of the ID_Click, and it still wont load the right values...it doesnt even save the values to the table anymore...


oh and danny, i did edit your code a tiny bit..instead of having most of my textboxes bound to a field, I am populating them manually, so that if the user edits the item it isnt automatically saved

[dannymking]

I thought I'd written that to just move the recordset, and the form was based on the recordset (Bound) so that any changes made should reflect directly into the table..

You shouldn't need to repopulate the form with the record's values as the movement of the recordset should handle this.. any unbound controls will need to be manipulated in the form_current event..

Me tinks... its gonna have to be an upload if possible...

[kfcSmitty]

an upload? and yeah...the problem was, if the user editted a bound form, the changes were automatically changed..but if they editted the ID or Name, then they would have to click a save button...very confusing

So I decided to make everything save with the click of the save button...but now this comes up...

The way I am saving the record is as follows

Dim rsSave As ADODB.Recordset
Dim SaveString As String
Set rsSave = New ADODB.Recordset
SaveString = "SELECT * FROM WorkerInformation WHERE ID ='" & ID.Value & "'"
rsSave.Open SaveString, cn, adOpenKeyset, adLockPessimistic, adCmdText
    'If rsSave.Fields("ID") = ID.Value Then
        rsSave.Fields("ID") = ID.Value
        rsSave.Fields("Name") = txtName.Value
        rsSave.Fields("Level") = Level.Value
        rsSave.Fields("Payband") = Payband.Value
        rsSave.Fields("MaxPayband") = MaxPayband.Value
        rsSave.Fields("FullSalary") = txtSalary.Value
        rsSave.Fields("Classification") = cboClassifications.Value
        rsSave.Fields("Account") = Account.Value
        rsSave.Update
        rsSave.Close
Call ID_Click

Which tends to save the record, but i have to click to the next record, and then back, before it shows the updated record

[dannymking]

By upload I mean, if possible upload a zip of the database with just the table and form.. You can sample the data if its confidential.. I would probably be able to fix it by seeing it, better than trying to write it.

You could possibly pull the recordset into memory of the form and then allow the user to change what they want.. the form_unload event would then repopulate the table with the information..

See if you can post the database and I'll take a look to see what I can do..

[kfcSmitty]

Okay..this is kind of confidential information....But I guess since the records I have here are all completely made up by me..and so are the #'s...it doesnt really matter at this point

the errors I am having are on frmWorkerInformation

[dannymking]

Looking at it... it's gonna take a while I think there could be some code in here that needs amending or possibly removing..

I see this has been done with ADO.. any objections to switching this to DAO, as I can work quicker and smarter with this.. plus I get a connection error on trying to Shift click my way into the database.. Obviously your start-up form sets this connection, I'll have to change that to a module, so I can re-initialise it from anywhere

[dannymking]

I think that we'll have to forget about the rep point for this one..

A family size bucket would be better..

[kfcSmitty]

yes, main form sets the connection

And I would rather you not switch to DAO...as I have absolutely no experience with it...

[dannymking]

It's a lot easier to get to grips with inside access than ADO

[kfcSmitty]

but im not having a problem calling the data or anything...the problem is with it staying on the same record and updaing the values.....

Sigh..I so shoulda taken an easier job this summer

[dannymking]

Double check that cose when I moved to a different record and then back again the changes had vanished...

[kfcSmitty]

blehh..its the same problem as yesterday....if i run form_current right after the save code goes..it errors. But if i Click a button after the save, it works fine!

[kfcSmitty]

works now I didn't think i could use Recordset..so I was using a new recordset to save...Using the form's recordset to do it worked like a charm

[dannymking]

Another approach to this would be to grab the recordset as an array, place the calculated values into an array with the same row dimension and the allow the user to make as many changes as they want.. but not actually place the values back into the table until the form closes..

Or possibly use a temporary holding table to store the changes and then delete the original and rename the temporary..

Well I'm glad you got it resolved...

Damn Gonna miss that bucket now...

[kfcSmitty]

bucket? o.0 And well its somewhat resolved lol..i can update everything except the level for some reason :/

and dude, you gained like 14 reps since yesterday :/

[dannymking]

Bucket... KFC.. you used to work there...

You didn't get that

I helped Hack out..

[kfcSmitty]

ahh bucket har har har Its been like a yeah and a half since i stopped working there (although theyve been bugging me to go back, so i might do it during the school year)


Programming is making my head hurt..my Payband, MaxPayband, Name, ID, and Salary fields all work properly..but my anniversary and level dont! lol stupid stupid

[dannymking]

shall we try DAO, arrays and a re-build of the form.. ??

ADO is trickier than DAO.. you'll pick it up very quickly and you'll see why M$ reset it as a default reference in Access 2K3

[kfcSmitty]

naw naw, now that its just down to some debugging with code i am familiar with this shouldnt be too much of a problem

Ive only given out 1 rep since yesterday 9 more to go till i can rep u again lol

[dannymking]

take your time.. I'm not out for the Rep (just keep getting it ) I need to find something to Rep yet..

[kfcSmitty]

finally works 100% now *marks resolved*