Udate data in a table from something on a form

[jpl458]

I have an app that has several very small tables that will need to be up dated by the user. Example: a list of phone numbers in a table called myphonenumbers. The may be up to 5 phone number in that table, and they are displayed on a form in a listbox. I want to have a separate form the contains whatevers that will up date those little tables. I want to have the change are to be small, like a text box or a listbox, I just can't figure out how to set them so the user can add, delete or modify the data in lets say the phone number table. I tried applying a query to a list box but that didn't work (should I have made it an update query?). I have no code to show because nothing works, so far. Hope this explanation makes sense.

 

[The_Doc_Man]

Generally, you have to get into some VBA code for this kind of thing because the .Rowsource for a list box or combo box isn't going though a method that allows editing. Usually if you want to add or remove data from a list you have a separate form tied to the list box's .RowSource table.

It IS possible for you click a button to build a record and do an INSERT INTO or to select an entry and then click a button to do a DELETE from that source (followed by a .Requery so the changes show up right away.) The question is, how comfortable are you with VBA? A "divide and conquer" method with two forms - one for table maintenance and the original form - will be easier than trying to build all of that at once in a single form.

 

[jpl458]

Generally, you have to get into some VBA code for this kind of thing because the .Rowsource for a list box or combo box isn't going though a method that allows editing. Usually if you want to add or remove data from a list you have a separate form tied to the list box's .RowSource table.

It IS possible for you click a button to build a record and do an INSERT INTO or to select an entry and then click a button to do a DELETE from that source (followed by a .Requery so the changes show up right away.) The question is, how comfortable are you with VBA? A "divide and conquer" method with two forms - one for table maintenance and the original form - will be easier than trying to build all of that at once in a single form.

I had set up a separate form that would only be used for updating the 4 or 5 little tables that users need to change occasionally. Example is a short list of a few phone numbers. Need a way for them to add and delete while staying the "Maintenance" form. In that table, when they click something a little table appears, they then edit/add/delete (just like you would do if you were looking at the table natively, when you allowed to change data). VBA is ok, but expressions with all that punctuation is really nit picky, especially for an old Assembler programmer. But you can get a lot done in a short amount of time. I was just reading about dragging and dropping data sheets that are the result of a query to the form, but it was an older article, and nothing worked.

Thanks for your time and answers

 

[The_Doc_Man]

The simplest ADD/MODIFY/DELETE option on your maintenance form would require you to have three extra buttons, one for each function.

For the DELETE function, you would (a) select a phone number and (b) click the "special" DELETE. Behind the scenes, you do something like this:

(WARNING: "Air" code - supply your own variables...)

IF listbox.ListIndex = -1 Then
    MsgBox "No number selected"
    Exit Sub
End If
XRow = listbox.ListIndex
XPhone = listbox.column( 0, xrow )
XSQL = "DELETE * FROM PHONELIST WHERE [PHONENUM] = '" & XPhone & "'"
CurrentDB.Execute XSQL, dbFailOnError
listbox.Requery

For the modify function, (a) select a number and (b) click the "special" MODIFY

(WARNING: "Air code" again)

IF listbox.ListIndex = -1 Then
    MsgBox "No number selected"
    Exit Sub
End If
XRow = listbox.ListIndex
XPhone = listbox.column(0, xrow)
XNew = InputBox( "Enter new phone number", "Change Existing Phone Number" )
XSQL = "UPDATE PHONELIST SET [PHONENUM] = '" & XNew & "' WHERE [PHONENUM] = '" & XPhone & "'"
CurrentDB.Execute XSQL, dbFailOnError
listbox.Requery

For the ADD function, just click the "special" ADD

(obligatory "air code" warning)

XNew = InputBox( "Enter new phone number", "Add new number")
XSQL = "INSERT INTO PHONELIST (PhoneNum) VALUES ('" & XNew & "')"
CurrentDB.Execute XSQL, dbFailOnError
listbox.Requery

Let's be clear, I was shooting from the hip. You have to create "OnClick" events for each function and put the appropriate code behind the appropriate button-click event. You will probably have to play with this. Note also that if you have an ORDER BY clause in the .RowSource for the listbox, the .Requery will apply the sort order to include the new or changed record and will "close up" the gap left by the DELETE.

 

[jpl458]

The simplest ADD/MODIFY/DELETE option on your maintenance form would require you to have three extra buttons, one for each function.

For the DELETE function, you would (a) select a phone number and (b) click the "special" DELETE. Behind the scenes, you do something like this:

(WARNING: "Air" code - supply your own variables...)

IF listbox.ListIndex = -1 Then
    MsgBox "No number selected"
    Exit Sub
End If
XRow = listbox.ListIndex
XPhone = listbox.column( 0, xrow )
XSQL = "DELETE * FROM PHONELIST WHERE [PHONENUM] = '" & XPhone & "'"
CurrentDB.Execute XSQL, dbFailOnError
listbox.Requery

For the modify function, (a) select a number and (b) click the "special" MODIFY

(WARNING: "Air code" again)

IF listbox.ListIndex = -1 Then
    MsgBox "No number selected"
    Exit Sub
End If
XRow = listbox.ListIndex
XPhone = listbox.column(0, xrow)
XNew = InputBox( "Enter new phone number", "Change Existing Phone Number" )
XSQL = "UPDATE PHONELIST SET [PHONENUM] = '" & XNew & "' WHERE [PHONENUM] = '" & XPhone & "'"
CurrentDB.Execute XSQL, dbFailOnError
listbox.Requery

For the ADD function, just click the "special" ADD

(obligatory "air code" warning)

XNew = InputBox( "Enter new phone number", "Add new number")
XSQL = "INSERT INTO PHONELIST (PhoneNum) VALUES ('" & XNew & "')"
CurrentDB.Execute XSQL, dbFailOnError
listbox.Requery

Let's be clear, I was shooting from the hip. You have to create "OnClick" events for each function and put the appropriate code behind the appropriate button-click event. You will probably have to play with this. Note also that if you have an ORDER BY clause in the .RowSource for the listbox, the .Requery will apply the sort order to include the new or changed record and will "close up" the gap left by the DELETE.

I will give this a go in the morning. I just seemed to me that there should be a way of opening a table in a control like a list box or textbox and have it act like the real table. But you can't always get what you want.

Really appreciate the help. Never would have figured it out.

 

[moke123]

Sounds like your describing something like this, similar to Docs solution

 

[jpl458]

Sounds like your describing something like this, similar to Docs solution

Microsoft blocked it because it was a potentially dangerous macro. Makes me worry after reading your tag line.

 

[jpl458]

Microsoft blocked it because it was a potentially dangerous macro. Makes me worry after reading your tag line.

But that is what I was looking for. Can you open a table in a subform and resize the subform?

 

[The_Doc_Man]

Can you open a table in a subform and resize the subform?

It is doable if you have the room on the main form. You cannot resize a subform to go beyond the boundaries of the parent form unless you resize the parent form first.

However, assuming you get to that point, if you have the sub-form open to the desired table that table also drives the list or combo box on the main form, you might get a case of self-interference since in that case you would have the same table open twice. At that point, your choice of query lock settings would govern what you could or couldn't do.

 

[moke123]

Microsoft blocked it because it was a potentially dangerous macro. Makes me worry after reading your tag line.

Thats the new microsoft thing when you download a file off the internet. You have to go right click on the file, select properties and you'll see a setting to unblock it.

How to Stop Windows 10 From Blocking Your Downloaded Files

If you can't open files you’ve downloaded or received via email, it’s likely that Windows 10 is blocking them. Here’s how you can fix this issue.

www.makeuseof.com

 

[Pat Hartman]

I must be missing something. Since the phone numbers are not related to the record on the form, you can make a subform and embed it in the main form with no master child links. If you can't spare the space, you can use a button to open a form and just make the changes there. Unless you have some complicated validation code, all we're talking about here for the popup is DoCmd.OpenForm to open a popup. Make sure it is set to dialog so it has to be closed to go back to the other form. Or, if it is a subform, you don't need any code at all. For a subform, you have a lot of control over how it looks on the main form. it will have 5 rows and two columns. If you never have to add a row, you can set the AllowAdditons property to No and turn off all the scroll bars since the rows and width are fixed.

 

[jpl458]

The simplest ADD/MODIFY/DELETE option on your maintenance form would require you to have three extra buttons, one for each function.

For the DELETE function, you would (a) select a phone number and (b) click the "special" DELETE. Behind the scenes, you do something like this:

(WARNING: "Air" code - supply your own variables...)

IF listbox.ListIndex = -1 Then
    MsgBox "No number selected"
    Exit Sub
End If
XRow = listbox.ListIndex
XPhone = listbox.column( 0, xrow )
XSQL = "DELETE * FROM PHONELIST WHERE [PHONENUM] = '" & XPhone & "'"
CurrentDB.Execute XSQL, dbFailOnError
listbox.Requery

For the modify function, (a) select a number and (b) click the "special" MODIFY

(WARNING: "Air code" again)

IF listbox.ListIndex = -1 Then
    MsgBox "No number selected"
    Exit Sub
End If
XRow = listbox.ListIndex
XPhone = listbox.column(0, xrow)
XNew = InputBox( "Enter new phone number", "Change Existing Phone Number" )
XSQL = "UPDATE PHONELIST SET [PHONENUM] = '" & XNew & "' WHERE [PHONENUM] = '" & XPhone & "'"
CurrentDB.Execute XSQL, dbFailOnError
listbox.Requery

For the ADD function, just click the "special" ADD

(obligatory "air code" warning)

XNew = InputBox( "Enter new phone number", "Add new number")
XSQL = "INSERT INTO PHONELIST (PhoneNum) VALUES ('" & XNew & "')"
CurrentDB.Execute XSQL, dbFailOnError
listbox.Requery

Let's be clear, I was shooting from the hip. You have to create "OnClick" events for each function and put the appropriate code behind the appropriate button-click event. You will probably have to play with this. Note also that if you have an ORDER BY clause in the .RowSource for the listbox, the .Requery will apply the sort order to include the new or changed record and will "close up" the gap left by the DELETE.

I have the listboxes setup as well as the add and delete buttons for each listbox. The Phone Number ad and delete work great. But, when I asked the question I forgot to include that the othere 2 listboxes have more than one column. In the vehicle table there are three columns: Make, Model, and VIN. Here is the code, and it does up the make, "

XNew = InputBox("Enter new Vehicle", "Add new Vehicle")
XSQL = "INSERT INTO Vehicltbl (Make) VALUES ('" & XNew & "')"
CurrentDb.Execute XSQL, dbFailOnError
setupvehicleslb.Requery

I cant figure out how to update the three columns. Can't figure out the syntax. Definitely need some help here. It seems to ma that the first line could be altered to allow the entry fo all three fields, but I cant get that to work. Then the INSERT needs to be changed, I'm guessing.

Here is a picture from the form:

Thannks again

 

[moke123]

Try the form approach like in my example. Just add the extra fields to the form.

 

[jpl458]

Thats the new microsoft thing when you download a file off the internet. You have to go right click on the file, select properties and you'll see a setting to unblock it.

View attachment 103463

How to Stop Windows 10 From Blocking Your Downloaded Files

If you can't open files you’ve downloaded or received via email, it’s likely that Windows 10 is blocking them. Here’s how you can fix this issue.

www.makeuseof.com

 

[jpl458]

Very cool. How would I do adds to a listbox that had 3 columns for a vehicle: Make, Model and VIN. Plus I need to be able to delete items from the list boxes. If you could give me an example I would appreciate it. I like its simplicity. I'm just not an ACCESS or VBA wizard.

Just sent some time figuring out your example. Very efficient and compact for a user. My problem is in the VBA, and doing it on say, a car table with Make, model and vin, both adds and deletes. Don't know how to reference more than 1 column if listbox has multiple columns.

Thanks

 

[moke123]

You only need to reference the 1st column of the list box which in this case is the primary key.

 

[jpl458]

You only need to reference the 1st column of the list box which in this case is the primary key.

Now I know what I'll be doing tomorrow. I'm impressed with the design.

Thanks so much.

 

[jpl458]

Sounds like your describing something like this, similar to Docs solution

I finally got to use the form you sent but am having problem that I can't figure out. I am trying to set up listboxes that allow the user to add and delete items from very small tables. The current problem has to do with deleting phone numbers from the list. I plagiarized your example a bit, by importing the sub screen in your example. Here is the listbox and the code:

(I copied your code and changed the names of the listbox and the file).

On the dblclick event the code is;

 If IsNull(Me.setupphonelb) Then Exit Sub
    DoCmd.OpenForm "Form2", , , "PhoneID = " & Me.setupphonelb, acFormEdit, acDialog
    Me.setupphonelb.Requery
    Me.setupphonelb = Null

The DoCmd.open form goes to this code for the subform:

If Me.NewRecord Then
        Me.LDelete.Visible = False
    Else
        Me.LDelete.Visible = True
    End If

The problem is the Me.NewRecord is alway set to -1, so the Me.LDelete.Visible = False is execute, ther fore I can't delete
Before the load of the subform Me.NewRecord is 0, but as soon as the subfrom is loaded Me.NewRecord becoms -1.

The result on the form is this:

It should look like this:

The Subform is opened without the delete button.
Tried to fix, but I am at the outer limit of my knowledge here.

Thanks

 

[Pat Hartman]

I have the listboxes setup as well as the add and delete buttons for each listbox.

You really are being persistent with these listboxes. Listboxes are meant for display. Forms are meant to be used for updating. You need NO code to use a subform but you need code for all actions to use a listbox. WHY????? Do you like writing unnecessary code?

 

[moke123]

Double check that the pop up form is set to modal in the property sheet.