Full Version: Changing posts in tables via forms
UtterAccess Forums > Microsoft® Access > Access Forms
oso
I am designing a database where I have forms that the user uses to fill in information to the database. When they enter new information they use one form and when only changing existing information they use another form. I use queries to gather the information for the forms.
How when changing information how do I create a form that doesn't save the changed information untill you press a button called save? And error handling etc when not pressing save?
When Deleting a record how do I auto update the information from the query instantly so the form is up to date at all times?
When adding new posts to a table i get existing posts with a query but I only want to add new posts without being able to change allready existing posts in the table?
dashiellx2000
If I understand correctly, what you want to do is use Unbound Forms. Then you can append/update the recordset as needed.
Peter46
If you are using a bound form (one that has a recordsource) you cannot prevent Access trying to save the record when you move off.
You can use the Form_Beforeupdate event to check for a flag that could be set in your Save button procedure, and Cancel the update if the flag is not set.
orm properties can be set to AllowAdditions= Yes/No and Allowedits= Yes/No.
Odon't understand the other q.
oso
Exactly how do I fix this flag thing in VBA?
The other question wasn't delete,
The correct question is: I have a button which marks items as "deleted" in the table and the querry doesn't display these posts. Hod do I update the form automatically after i have pushed this button so the "deleted" post dissapears?
dashiellx2000
I'll leave the Flag thing to Peter as I think I know where he's going, but I'm not 100% sure.
If I understand the question, you have a button on the form which marks the account as Deleted and the query will not show these accounts in the form again. As long as the button is working correctly, all you'd need to do is requery your source. Add Me.Requery to the code that marks the record as deleted.
HTH.
Peter46
The Flag thing...
It the top of the form module, below OptionCompareDatabase/Option Explicit you declare a module level Boolean variable as your flag.
Dim OKToSave as Boolean
In the Form_Current event procedure you need to add:
OKToSave = False
In your save button code, you need to add:
OKToSave = True
In the Form_BeforeUpdate procedure you put:
If OkToSave = true then exit sub
if msgbox("Is it OK to save changes?", vbyesno + vbquestion, "Save Record") = vbYes then exit sub
Cancel = True ' stop record from saving
There might be one or two loose ends to tie up when you test this; it depends on how your form is being used.
(I have assumed that there is no other code in these event procedures.)
dashiellx2000
That's a nice idea Peter. o!
oso
Thx a lot guys, it works like a charm now.
oso
Got a New question guys...
On the main form where u enter a project number u get info on current subprojects in this project number.
To ad new subprojects to the main project u click a button to open a form to add new subprojects.
I have a form that is unbound that the user use to add new posts (representing subprojects) to a table .
Now I want the main project number in the unbound form to be the same as the main project number in the main form by default. Tried to program this in vba but couldnt make it work.
The second question is:
one field in the table is in the format PM, but the information i enter in the text box in the unbound form doesnt seem to be saved to the field in the table. Works for all other fields, just the PM field that doesnt work.
dashiellx2000
For your first question:
On the form where you are entering your subprojects you should have a control that records the main projects Primary Key. If the main form is still open (you can hide it if you don't want the screen too cluttered) just set the defaul value of the ProjectID control to: =[Forms]![frmMainForm]![ProjectID]. You could also transfer the projectID by using the Open Args on the command that opens the subproject form.
I'm not sure I understand your second question. How are you saving this information?
oso
Back here again then. The thing with primary key is solved, thx.
Am using this code in vba, some comments are in swedish:
Private Sub cmdSpara_Click()
On Error GoTo cmdSpara_Click_Err
Dim rst As New ADODB.Recordset
rst.Open "tblÅtgärdsprogramm", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
With rst
'Lägg till ny åtgärd vid slutet av Recordset objektet
.AddNew
![intFastighetsnummer] = intFghnr 'Lägger till data
![chrÅtgärdstyp] = cboÅtgtyp
![chrStatus] = cboStatus
![intUtfall2006] = intUtfall2006
![int2006] = int2006
![int2007] = int2007
![int2008] = int2008
![int2009] = int2009
![chrKommentar] = chrKommenatar
.Update
End With
rst.Close
Set rst = Nothing
cmdSpara_Click_Exit:
Exit Sub
cmdSpara_Click_Err:
MsgBox Err.Description
Resume cmdSpara_Click_Err

End Sub
This works for all fields in the table tblÅtgärdsprogramm but the field [chrKommentar] is in the format PM in the table. When entering the info into the text box and then pressing the save button everything is put into the table accept the comments in the chrkommentar textbox.
Plz help me
jsitraining
OK, again, what is a PM format, I am not familiar with this (Is it a new format to 2003, is it a number, character or date format) Whay should a format be an issue?

The variables that you are using in your code, are they declared anywhere? (They are not declared in the code that you posted.
when you hit the line
![chrKommentar] = chrKommenatar

what is the value of chrKommenatar. Is it what you expect?

Jim
Also if you have columns called int2006, int2007, int2008
you have design errors in your tables (repeating groups) which should be addressed before you go any further
Edited by: jsitraining on Thu Dec 22 9:47:33 EST 2005.
oso
Sorry about PM thing, in english access it is called Memo....
Why is it a error with int2006... etc
they represent different years, in which costs for each project is entered.
the variables are textboxes and comboboxes in a form where i gather the info from.
jsitraining
Cool.
couple of points.
If it is a Memo field then this is not a Format, it is a Datatype. It is very important to make the distinction wink.gif
int2006 is an error because it represents repeating Groups. This goes very strongly against the rules of Normalization. You should have a single costs column with an additional column representing the year of the costs:
CODE
Cost         Year
500          2006
700          2007

When referencing a control on the form it is generally best to fully qualify the object:
If the Module is part of the form use
Me.ControlName
If the Module is not attached to the form use
Forms.FormName.ControlName
Try not to have control names the same as column names, this can cause issues
Change the names of the controls.
Again
when you hit the line
![chrKommentar] = chrKommenatar
what is the value of chrKommenatar. Is it what you expect?
Jim
oso
When I hit the line ![chrKommentar] = chrKommenatar the info is not registered into the table at all.
so there i sno value in it.


If i normalize the table for int2006 etc how do i solve the following?

The form looks like this when displaying or adding info with one kolumn for each field in table.

Id Type Status 2006 2007 2008 2009 Kommentar
____________________________________________________
box box box box box box box box


How do i get the correct cost in the correct textbox for the year of the cost?


Edited by: oso on Fri Dec 23 5:05:39 EST 2005.
jsitraining
This would require multiple insert statements. Each cost is a separate transaction.
Jim
oso
Back again after the holidays, how do i get the information in the memo field to be saved correctly into teh table? as of now nothing is saved to that field.
oso
When Normalization of the table so the design is as following:
ntID
chrÅtgärdstyp
chrStatus
intutfall
intYear
intCost
chrkommentar
dtmLastchange
blndeleted
How does the code look to get the cost for intYear to end up in the right textbox representing the year for that cost. I have 4 textboxes representin 2006, 2007, 2008, 2009 and i want the cost to appear in the correct box.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.