Hi everybody!

I've created an Edit Form that has a text field where user can enter a Record Number with a Search button next to it. Once the search button is pressed it finds the record and displays it below.

The problem is that when I change a value of a field in the Form View it changes immidately in the table even before I hit Save button.

So lets say I have a record # 8 up. I see a student name and his/her grade. If I change Grade from A to B and click on Search button again, the Grade will change in the table from A to B

Is there a way to make it change only after I hit save button?

Can someone please help me?

Thank you,
BLK

A point of instruction for you:

1. Forms will automatically save the data when you move from record to record or when the form closes.

2. If you wish to avoid this, then you can set code in the form's Before Update event to give you the chance to cancel the operation if you wish.

3. You can do this in conjunction with a button, but you would still need to handle things in the form's Before Update event.


So, in the Before Update event you can use it like:


If MsgBox ("Are you sure you want to save?",vbQuestion + vbYesNo,"Save Confirmation") = vbNo Then
Cancel = True
Me.Undo
End If

And then if you choose YES it will save but if you say NO it won't.

Bob,

Thank you, but I still have a problem.

I didn't mention one thing. Inside the form I have a tab control with 3 pages. Each page displays information from its own table. The way it's working now is when a user presses a search button the record source changes depening on what page's search button user clicked.

Forms!frmEdit.RecordSource= "RecNum1"
RecNum1 is the query that returns a Record number that user entered in the text field.

So right now, when I press Search button again, it asks me that question vbYesNo but when I click No it goes back to Search1_Click event that trying to get the RecordSource

Can you think of an easier way? I tried to do on Page Click, but it seems that I have to click on the middle of the page for the RecordSource to change and not just on the name of the page at the top. Is there a way to do it on PageIndex? or somehow do it in the Form Properties?

BLK

If you have subforms, you would have to have this in the Before Update event of each of those forms.

No I don't have subforms. I just have 3 Pages on one tab control.

And each with different tables? That doesn't sound right; or good.

Correct, each page has its own questions and its Table. It's and Observation tool. For different department. So depending on which page user clicks, it will bring the fields of that table into a form for an edit.

I believe that is the limiting factor. You're changing recordsets, if I understand you right. What might be more manageable is to use 3 subforms so that you can manage the events easier and better. Just a suggestion, but without using completely unbound forms, you won't be able to have an "overall" do you want to update message.

Yes, like bob said. You have bound the textbox control to the data field.

You have to unbound the control and use VBA code to navigate between records.

I never use bound control unless it is for presentation.

---------------------

I found using subform in any situation is annoying. I have already completely abandoned subform years ago. I only use listbox now, that needs some extra work though.

I really don't know how to use unbound text boxes to bring information from the table into a form.

Can someone please tell me how it's done? Or at least point me into the right direction?

I also read somewhere that people use temporary tables to grab data from actual table, then display it in the form, then after save is clicked you append that data to original table... don't know how to do that either. I am not that advanced in vb.

Any help would be nice.

Thank you guys!

I really don't know how to use unbound text boxes to bring information from the table into a form.
1. Open the form in design view
2. Click on the control to select it
3. Open the properties dialog, if you haven't already
4. Click on the Data Tab and look for the property called Control Source
5. Highlight the field name that is in there
6. Click your delete button.

I also read somewhere that people use temporary tables to grab data from actual table, then display it in the form, then after save is clicked you append that data to original table...
I don't suggest doing that unless absolutely necessary. You can use a query exactly like it was a table and you don't end up creating bloating issues and other potential corruption problems. If you have a very large dataset that you have to pull from, then it might be good to use a temp table. But, don't just assume that a temp table is necessary for reports, because it isn't.

Bob, after I delete control source, how do I bring data into that field?

Bob, after I delete control source, how do I bring data into that field?

The combo box that you don't have a bound column is the one that does the search. You don't save data in that field. If you need to select and save data that can be a bound combo box, but if you were talking about the combo box where you make a selection and then it moves to the appropriate record, that is an UNBOUND combo and no data is stored from it, just a selection made and code run in it's After Update event.

That's what I had before. I had unbound field where user enters a row ID then by clicking a search button that Row ID along with it's fields is displayed on the form in its own text boxes. Like Call_Date, Student ID, Student Name, Student Grade etc...
What was aikea in the post above talking about? "You have to unbound the control and use VBA code to navigate between records."???

Is it possible for you to post your db? It might speed along our assistance.

I'll do it first thing tomorrow morning.

Thanks Bob!

Why not just use VBA in "Form_Load".

Create a connect to the table then retrieve the data and fill the form. It need some programming, but this is the only way to decouple your data from the form(user interface).

It offers you much better control over your data. This is the only sollution if concurrency is an issue.

Hey guys, here is the sample db attached. Once you click on Find button the The record source is changing depending on the page you are at.

Hi Bob,
Have you had a chance to take a look at my db? Still trying to get it to work.

Thanks,
B.

1. Your form is corrupt. The events would not fire properly but when I imported it to a different database it worked.

2. You have no code in the TabCtl0 On Change event to determine which recordset to use. For example:

Private Sub TabCtl0_Change()
Select Case TabCtl0.Value
Case 0
whatever here
Case 1
whatever here
End Select
End Sub



2. Instead of using it like this, you should really be using subforms.

I haven't got time to look at your database blk133, but I am strongly against using subform in any scenario. I have got too much pain from it when few years ago I started my access study.

The best solution is. Create a query which you want you subform to present. Add a lisbox to the form, change its rowsource property to the query you create, change bound column to the column you want to use as the value of selected data (primary key normally).

Now you have a subform that decouple from the data source. You can use listbox1.value to retrieve the value of selected item.

You can create a query with parameter, when you need to change the criteria, just give a new value to that query then requery the listbox.

I have it set so that once you click the Search button in changes the recordset.

But I like your idea better. I'll change it.
Could you tell me how can I bring in the data from the table into unbound text box on the form? What is the code for that? I don't want to use combo box or list box.

Check out the database attached.

You search table "Book" by book id and represent result in a list box.

aikea,
Thanks for the attachment, but I want every table field to be displayed in the text box so that user can modify any field he/she likes. Just like I have it in my attachment. What I want is for change to take effect only after Save button is pressed.

I think you need some serious VBA and ADO lesson. Add a new record in ADO is quite basic for an Access developer.

Dim rs as new adodb.recordset
rs.activeconnection=currentproject.connection
rs.source=tablename
rs.open
do while not rs.eof
if rs["id"].value = idyouaresearching then
rs["fieldname"].value=valueyouwanttochange
end if
rs.movenext
loop
rs.close