How do I create a vba function to add a row in MS Access?

Joseph S
Joseph S used Ask the Experts™
on
I would like to create a vba expression to add and remove rows by using a combo box containing a value list of 1-40. For example, if I select 3 from the value list, it will create 3 rows. If I put the list to null then 0 rows or hidden. The row consist of 3 combo boxes, and 5 text boxes. How do I create a vba function for such thing?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Something like this:

    Dim RS As DAO.Recordset
    Dim Count As Integer           
    
    Set RS = CurrentDb.OpenRecordset("Select Top 1 * from YourTable")
    
    For Count = 0 To Nz(Me!ComboCountOfNewRecords.Value, 0)
        RS.AddNew
            RS!SomeField.Value = SomeValue
            RS!OtherField.Value = SomeOtherValue
            ' Etc.
        RS.Update
    Next
    RS.Close

Open in new window

Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
I would like to create a vba expression to add and remove rows by using a combo box containing a value list of 1-40. For example, if I select 3 from the value list, it will create 3 rows. If I put the list to null then 0 rows or hidden. The row consist of 3 combo boxes, and 5 text boxes. How do I create a vba function for such thing?
Please, elaborate.
And keep in mind that a relational database does not work like a spreadsheet, adding a row can be done with a reacordset (as Gustav suggested) or an INSERT query, but providing no value make little to no sens.

Author

Commented:
I attached a File to show yoy what I would like to do. The file only works for text boxes, and I am not able to change the record source to design one. I also need to convert 3 of the text boxes to combo. When I do that it misses up the table.
FakeTable---v2--1-.accdb
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
So you are creating controls (textboxes), not records. That was not easy to guess.

The easy method is to create the form having the maximum count of rows and columns, and then hide them all.
Then, after having selected the counts, unhide the rows and columns as requested.
Much simpler and faster, and no trouble positioning and sizing the textboxes.
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
Hmm, dynamic GUI within ms Access can be troublesome, as it wasn't designed for that purpose.
Toggling control's visibility is the best you can do (so your mdlControl module is just a "No, no no !!")

Side note:
- Your number of rows combo-box should start at 0 (a table without rows make sens).
- Your number of columns combo-box should start at 1 (a table without rows make no sens).
- Both your combo-box should provide a default value (null as a number of rows or columns make no sens).
- Your FormatTable function break the SRP, move all the cosmetic stuffs in separate functions.
- You don't need to requery a form if there is no change to data.
- Don't think the "dummy" control is needed at all.
- Instead of a function hiding everything plus another making controls visible, write one that set controls visibility directly (more efficient).
Private Sub SetColumnsVisibility(ByVal numberOfColumns As Integer)
    Dim ctl As Access.Control
    For Each ctl In Me.Controls
        If (Mid(ctl.Name, 4) <= numberOfColumns) Then
            ctl.Visible = True
        Else
            ctl.Visible = False
        End If
    Next
End Sub

Open in new window


Attached is your updated database, all cosmetic stuffs are commented out, I leave that up to you.
FakeTable---v2--1-.accdb
Distinguished Expert 2017

Commented:
Joseph,
We have no idea what you are trying to do.  YOU decided the solution that you want US to implement for you.  What you have determined is the "solution" is wrong on many levels.  You should remove your spreadsheet hat and spend some time learning relational database design.

Of course if you explained what problem you are trying to solve rather than what you think the solution should be, we would be in a better position to help you.
John TsioumprisSoftware & Systems Engineer

Commented:
Well there are a lot of thing that are doable but probably we need some more info on what and why you try to accomplish.
Lets assume that you simply want to create controls...then Application.CreateControl is the way you do it...you give the form you want to create the control...size,position parameters ...later name and that't it...
But more important is the functionality you wish to achieve...so maybe there are other easier ways to achieve what you want.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial