Update table through textbox

[Danielt949]

Evening all

I have Tbl_Location, and to add a new entry the user will open Frm_New_Location

In Frm_New_Location the listbox will populate with everything that is listed in the table. User is to check to see if the value is there before they enter a new one.

When the user adds a new Location, they enter it into the textbox and click the "Add Record" button.

Userform automatically updates with the new record in place.



If the user DoubleClicks on a record in the listbox, there is another textbox that becomes visible.

What i need help with is, What ever item is selected in the listbox, it is then populated in the new textbox, they can edit it or delete it, and it saves in the old records spot instead of creating a new one.
I am learning as i go and am not quite familiar with recovering of information for the purpose of editing.Test.accdb

Can you please assist.

[June7]

Add this line to the List9 DoubleClick event:

Me.Text_Edit_Record = Me.List9.Column(1)


Code to UPDATE existing record:

CurrentDb.Execute "UPDATE Tbl_Location SET Location = '" & Me.Text_Edit_Record & "' WHERE Location_ID=" & Me.List9

Figure out what event you want code in - textbox AfterUpdate or a button Click.


Opening recordset to add a new record is okay or could Execute an INSERT action SQL, similar to what I show with UPDATE action. If you are concerned about SQL injection, go with opening recordset with filter for the specific record to UPDATE.


Usually, the easiest way to accomplish adding and editing records is with BOUND forms and controls.

[Danielt949]

Amazing.
Thank you.
I wish i had the knowledge you all have, it will take me years to get there.

Thanks again

[Danielt949]

CMMS-21.zip

I put your code to the test on a smaller form and it is working perfectly. However when i change it over to my working project, it is coming up with an error. Too few parameters.

Can you please assist a little more and explain where i have gone wrong.

Thank you

[mike60smart]

Hi Daniel
Took a look at your table structures and the following issues noted:-

tblComponents
Asset_Type field set as ShortText and is Looking up vales from tbl_Asset_Type

tblAssets
Asset_Location set as ShortText and is Looking up vales from tbl_Asset_Location
Asset_Type set as ShortText and is Looking up vales from tbl_Asset_Type

tblDefects
Asset_Location set as Number and is Looking up vales from tbl_Asset_Location
Asset_Type set as ShortText and is Looking up vales from tbl_Asset_Type
Asset_Number set as ShortText and is Looking up vales from tbl_Assets
Defect_Category set as ShortText and is Looking up vales from tbl_Category
Defect-Status you have Hard Coded the values. This is not recommended as in the future
you may add a new Status which means you would have to have access to the table to make changes.
The values which you have Hard Coded in the table should be in a separate table.

All of these issues mean you should rename each field so that they have an ID Suffix eg Asset_TypeID, Asset_NumberID, Asset_LocationID etc...
All of these fields should be Number Data Type - Long Integer.
Then you would set Referential Integrity between the Main Table and Related Lookup Table Lists.
You then create Combobox's on your Data Input Forms to lookup the specific values needed.

[Micron]

@Danielt949; If doing a major re-design, consider a combo for the areas and use the Not In List event thus eliminating extra form(s) and controls (listbox). Don't know if mike60smart's comment about look ups refers to what I think it does. If so you should read this
The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm

Many times I've posted links to help novices and if you want to save yourself some grief down the road, consider reviewing. See post 2 here
https://www.accessforums.net/showthread.php?t=88502

[June7]

You have a relationship set in Relationships linking Tbl_Defects to Tbl_Assets. I suggest renaming Asset_Location to AssetID_FK. Then remove Asset_Type and Asset_Number fields from Tbl_Defects as this info can be retrieved in query joining tables.

Which form are you trying to code?

[Danielt949]

Thank you all for your responses. I am completely novice to access and alot of the information you have just thrown at me makes absolutely no sense and although i understand you are trying to assist, majority of the information above is not even in reference to the problem i am having. Well i dont think it is anyways, perhaps i am wrong.

June7, mate, that code you supplied above, i tried it in the basic userform and it worked perfectly, I moved it the my project and used it in my Frm_New_Location form, and it worked there also
when i moved and altered the code to Frm_New_AssetType_Category_Component, it failed to work.

I attempted to hide all irrelevent tables and forms etc etc, so that all that was showing is the one with an issue in it.
the form i am working with is the "Frm_New_AssetType_Category_Component"

Click the button "Edit / Rename Asset Type"
Select from listbox the item requiring rename
This then populates the textbox below.
Type in new name
Click button Rename record

This is when the error pops up. Run-time error 3061, Too few parameters, Expected 1

The code can be found close to the bottom of the userform code page in sub Btn_Edit_Asset_Type_Click()
CMMS-21 (2).zip
Thanks mate

[June7]

Field name [Asset_Type_ ID] in table has an extraneous space in front of ID. Remove it.


Should probably have some code to check that textbox is populated before trying to execute INSERT or UPDATE.