Comaparing user input value from form to values stored in a Db Column

[englishkid19]

Hi All,
This must have been covered before : looking for pointers please .

I have a userform with an "Add Button/Submit".

The user populates 5 to 6 text and number boxes then clicks submit. I have that all working and it adds records to the db.

The part i need help with is an UPDATE statment. so if a user makes errors in there initial submission , i want them to be able to UPDATE the record they have entered....

So essentially it needs to be something like this :

IF Me.DateBox.Value & Me.ResourceBase.Value = Input.DateAdded & Input.ResourceBase = TRUE
<<so it compares the date & resourcebase against dates and resource base combinations in the table >>
THEN
" Execute my update statement"
ELSE IF
Evaluate next row of database
<< i could probably do with another statment like " The combination of resource base and date you are trying to update are not present in table >>
END IF

any pointers greatlly appreciated

 

[welshgasman]

Why not just bind the form?

Hi All,
This must have been covered before : looking for pointers please .

I have a userform with an "Add Button/Submit".

The user populates 5 to 6 text and number boxes then clicks submit. I have that all working and it adds records to the db.

The part i need help with is an UPDATE statment. so if a user makes errors in there initial submission , i want them to be able to UPDATE the record they have entered....

So essentially it needs to be something like this :

IF Me.DateBox.Value & Me.ResourceBase.Value = Input.DateAdded & Input.ResourceBase = TRUE
<<so it="" compares="" the="" date="" &="" resourcebase="" against="" dates="" and="" resource="" base="" combinations="" in="" table="">>
THEN
" Execute my update statement"
ELSE IF
Evaluate next row of database
<< i could probably do with another statment like " The combination of resource base and date you are trying to update are not present in table >>
END IF

any pointers greatlly appreciated

</so>

 

[FlammConsulting]

A couple of things:
1) you don't need "=TRUE" in the IF statement - the expression xxx=yyy will return true or false, which the IF statement will process
2) If your form is bound to a table (INPUT?), your code will only compare the user entry to the CURRENT RECORD associated with the form. More importantly, the second the user starts typing in the bound textboxes, Access will be creating an pending update for the current record, which is not what you want. In order to enable update, you need a search function; if the form is bound to a table, you probably want to have 2 unbound input boxes for DateBox and ResourceBase, then trigger a search after the user enters those 2 fields. If the matching record is found, make it the current record and display the values in the form. Once the user updates the values, you'll fire an update statement when the submit button is pressed. You might need to set a boolean flag to indicate whether to do an update or an insert. If a matching record is NOT found, populate the 2 fields in a new record with the user input, then let the user complete the form and fire an insert when the submit button is pressed.
3) To facilitate the search, create a UNIQUE index on the underlying table which contains DateAdded and ResourceBase. This will prevent the creation of duplicate records and make the search process much faster.