update existing data on table through form (1 Viewer)

luzz

Registered User.
Local time
Today, 13:46
Joined
Aug 23, 2017
Messages
346
Hi there, how can i update data that is saved on the table through form?

Currently, i have a form modify, and a listbox that is created by using data from the table. So let say i want to modify on one of the PO number that already exist in the table, by retrieving the data to the form and after i am done with the modification, i clicked saved, the table should only save the latest record
 

Minty

AWF VIP
Local time
Today, 20:46
Joined
Jul 26, 2013
Messages
10,355
Simply bind the form to the table for editing.
You know that you are working on the live data, and that anyone else can't edit the same record, whilst you are using it.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:46
Joined
Feb 19, 2002
Messages
42,976
Access handles much of this for you automagically without any code.

Start by creating a bound form. You can use the wizard or create it from scratch by just starting the form designer to a blank form and adding a table or query to the RecordSource property.

Once the form is created, add a combo to the form header. If wizards are enabled, the wizard can help. It will give you three choices. Choose the one that says find a record on this form. The wizard will create an embedded macro - you can convert it to VBA if you prefer (I never use embedded macros even for simple things because when I look at the code module, I want to see ALL the form's code not just some of it).

You can use the combo to move to the record you want. Simply typing into a field will replace its value as soon as you either save the record or move to a different record. Access won't leave a dirty record hanging, it ALWAYS saves your data when you leave a record. Some people find this disconcerting and work very hard to fight with Access over when a record gets saved. Save your energy. Spend your coding time in the Form's BeforeUpdate event validating that all required fields have been entered and that populated fields have reasonable values. For example, it makes no sense to have a BirthDate in the future. You should also validate all dates for sanity. something like 11/22/208 is an easy typo to make and yet to Access it is a valid date since the year 208 is within the range of date values supported by the date data type. In the Form's BeforeUpdate event, you can prevent a record from being saved if you determine that there is an error in the data. You can leave the record dirty so the user will be prompted again if he tries to save or you can remove all the edits the user made - Draconian and I do it ONLY in TWO situations - the user is not authorized to change a record or if I prompt and he answers that he doesn't want to save.
 

luzz

Registered User.
Local time
Today, 13:46
Joined
Aug 23, 2017
Messages
346
Thank you both for the help, i have created a bound form and it works! But how can i use search for keyword on a bound form? i am able to search keyword on my unbound form
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:46
Joined
Feb 19, 2002
Messages
42,976
I mostly work with SQL Server databases so I never use filters but if the BE is Jet or ACE and the table isn't too large, you can use filters to do this.

Did you make the combo using the wizard as I suggested?
 

Minty

AWF VIP
Local time
Today, 20:46
Joined
Jul 26, 2013
Messages
10,355
Create an unbound control on the form and either filter the records as Pat suggested, or requery to only bring in the records you need.

On large recordsets the best method is to load the form with an "empty" recordset, Something like where PrimaryID=0 then load a specific recordset based on the forms search criteria.

An excellent example of this would be here http://allenbrowne.com/ser-62.html - this uses a continuous form, but can equally be applied to a single form with more controls and the use of record selectors.
 

luzz

Registered User.
Local time
Today, 13:46
Joined
Aug 23, 2017
Messages
346
I mostly work with SQL Server databases so I never use filters but if the BE is Jet or ACE and the table isn't too large, you can use filters to do this.

Did you make the combo using the wizard as I suggested?

Hi, yes i use two combo box, one for PO the other for colour. I have no issue having the values in the combo box. However, when i select PO 1 and colour blue, it will jump to record blue but the PO is 2.

How can i make the record to jump to the value that i selected based on the two combo box?
 

Minty

AWF VIP
Local time
Today, 20:46
Joined
Jul 26, 2013
Messages
10,355
How have you set up the filter from the combo's?
 

Minty

AWF VIP
Local time
Today, 20:46
Joined
Jul 26, 2013
Messages
10,355
So what is happening when you change the combo value ? I'm confused.
Is this a unbound combo?
 

luzz

Registered User.
Local time
Today, 13:46
Joined
Aug 23, 2017
Messages
346
So what is happening when you change the combo value ? I'm confused.
Is this a unbound combo?

Oh, when i click on the value 1 in the PO combo box, it will filter the PO 1
It is not a unbound combo
 

Minty

AWF VIP
Local time
Today, 20:46
Joined
Jul 26, 2013
Messages
10,355
If it is bound then you would be changing the underlying data, not filtering!

To make a filter based on the combo it must be unbound - normally you would put it in the header of the form.

You would then use some VBA to perform the filtering. Did you look at the example on Allen Browne's site?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 16:46
Joined
Feb 19, 2002
Messages
42,976
I didn't realize that you have multiple criteria. That means that the wizard can't help you. it only works with single criteria.

When I have multiple criteria, I always use a "filter" button to avoid premature filtering.

There is no code in the combo fields. All the code is in the "filter" button and all it does it to requery the form.

Me.Requery

The RecordSource for the form must be a query and the query has criteria.

WHERE (fld1 = Forms!yourform!cbo1 OR Forms!yourform!cbo1 Is Null) AND (fld2 = Forms!yourform!cbo2 OR Forms!yourform!cbo2 Is Null)

This allows either or both to be selected. If both combos are required, then leave out the " OR Forms!yourform!xxx" part of each expression. This is what makes the criteria optional.
 

Users who are viewing this thread

Top Bottom