Pop up input boxes

spectrolab

Registered User.
Local time
Tomorrow, 04:13
Joined
Feb 9, 2005
Messages
119
Hi Guys,

I posted on this forum a few months ago chasing a solution and still haven't found something. Is ther any way in vb that you can prompt the user to input a number? I was posted the code below to try and help. In the example below, i would like to be able to have the end user input the first and last numbers (11000 and 11100, in the example) in a pop up box, as these vary, not have them defined in the code, is this possible? In the database, the first and last numbers in a string are all that is needed and the db fills in the gaps, so to speak


Code:
Const MyTable As String = "YourTableName"
Const MyField As String = "YourFieldName"
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intCounter As Integer
Set db = CurrentDb
Set rs = db.OpenRecordset(MyTable)
    For intCounter = 11000 To 11100
        rs.AddNew
        rs.Fields(MyField) = "TP" & intCounter
        rs.Update
    Next intCounter
    rs.Close
    db.Close
Set rs = Nothing
Set db = Nothing

Thanks for any help you can give. I hope it is pretty straight forward, as my vba skills are fairly limited.
 
I would avoid using input boxes, I think you would be better off making a form to do this.

Start a new form, add two text boxes to it, name them something like "txtStartValue" and "txtEndValue" add a command button, and place the code shown (your code) within the command button code block.

Change this line:
For intCounter = 11000 To 11100

To:
For intCounter = me.txtStartValue To me.txtEndValue

You may need to add some that code to the after update event of the text boxes to check that the user has entered a number and not text. You may also want to check that end value is greater than the start value.
 
Thanks Gizmo, i didn't even think of that, makes life much easier!
 
That works perfectly, thanks a lot. I have another little problem I hope you can help with. The table I am updating has another field in it that needs to be the same for all the new records added. Is there any easy way to do this? Previously, I put it in a form in an unbound text box and put a bound text box hidden in the form which had the default of the entered text, linked to the field in the table. When you entered new records into the table (much like the code does now, only one by one) the default value was entered into that field.

I hope you can follow what I am saying, if not, I have test db that I can post which might help you follow.

Thaks again for your help!
 
spectrolab said:
field in it that needs to be the same for all the new records added

I am not 100% sure without looking it up, but I think you can do this.

Add an extra line to this:
For intCounter = 11000 To 11100
rs.AddNew
rs.Fields(MyField) = "TP" & intCounter
rs.Update

so it looks like this:
For intCounter = 11000 To 11100
rs.AddNew
rs.Fields(MyField) = "TP" & intCounter
rs.Fields("fldWithDefault") = "Default Value Here"
rs.Update
 
Thanks Gizmo, works like a charm.

I just ordered Access vba for Dummies, I think I need it!
 

Users who are viewing this thread

Back
Top Bottom