Display incremented value in form

[scottdg]

Once I refreshed I was able to view the parsed data. I did that just for testing (baby steps). Now I will see if I can convert and increment.



I already have the max value from the table so now I need to increment so I don't think I need DMax.

[ItsMe]

It takes some getting used to in order to break off small bits before you start chewing. Post up if you get stuck.

[scottdg]

Okay so I am just about there. I have been able to convert the number and add the leading zeroes back. The only thing I really need to do now is increment the number for each row in the form. So on form load it is finding the max value from the table and I need it to add one to the first row and then keep adding one to that value for each subsequent row for all ten records in the form. I am sure it needs to be in a loop but I am not sure how to refer to each row in the form.

[ItsMe]

You can append records to a table using SQL or DAO. Here is an example using INSERT with SQL to append new records to a field named ID within a table named tblMain. Understand there is not any error trapping here. If something goes wrong with your code, there will be a problem with your data's referential integrity.

Dim lngID As Long
Dim lngResult As Long
Dim strSQL As String

'Use this is you need to append new records to a table
strSQL = "INSERT INTO tblMain (ID) SELECT "

'I would use DMax here but you already have
'the value in a control on your form
lngID = Me.ID + 1

    For lngResult = lngID To lngID + 9
        CurrentDb.Execute strSQL & lngResult
    Next

[scottdg]

Writing it back to the table is not a problem. I created a table that takes the Max log number from import table and added it to my query. So right now my form has a list of ten people and the number 0002800 for each of them. I need them to show in the form as 0002801, 0002802, 0002803, etc up to 0002810. I already have a button that will then save each of them to the imported table with the new log number.

Right now I have the text box bound to the ConvLogNo in my query to display the 0002800 value and I have the following code on form load. Shouldn't this at least change the number to 0002801 for all ten people? It is still 0002800 for all of them.

Private Sub Form_Load()
Dim LogNo As Long
LogNo = Me!ConvLogNo + 1
End Sub

[ItsMe]

According to post #18 you need to append 10 records, incrementing a number value by one. The code you posted in post #20 increments a single record. You stated you needed to increment 10 rows. I suspect these will need to be new records. If you already have 10 records and you need to UPDATE each of these records, you will need to identify each of these records Primary Key value before updating the record(s).

I do not understand why you created another table, as explained in post #20. Perhaps you are struggling more than necessary because your DB has poor table design and your entities, along with their relationships, are not defined in a way to promote normalization.

Perhaps you can post your DB here. I should have a chance to look at it later in the day. Remove sensitive information, compact and repair, then zip file.

[scottdg]

Thanks again for your help.

First I meant to say I created a query to get the max value from the imported table.

I do need to append ten rows to the import table. But I want to show them on the form with the incremented log number. They will then be appended when clicking a button. In addition to not knowing how to increment the records for all ten I posted the code because it is not incrementing for even the first. My query that takes the max value from the import table has a value of 15-0002800. I added that query to the one that gets the random 10 customers from the linked table, parsed and converted it to a number so it now displays 0002800. So when I run my query I get ten records in the following format:

Scott 123 Main St 0002800
Frank 222 Easy St 0002800
Mary 333 Bank St 0002800
etc.

That is what I expected to happen. But that being the case I thought the code above should make the number 0002801 for each of them but it isn't. I was doing to see if I could increment any of them before worrying about incrementing all ten with ten sequential numbers.

So again the desired result in my form should be the following with all ten being appended to the import table:

Scott 123 Main St 0002801
Frank 222 Easy St 0002802
Mary 333 Bank St 0002803
etc.

[scottdg]

I realized that I did not make my textbox = to the logNo variable I just created. When I do that though it says the recordset is not updateable. The textbox is enabled so I am not sure where to change that.

[ItsMe]

...So again the desired result in my form should be...

You need to realize the data in the tables before you can realize the data in a form. Check you data in the tables to see if what you did was a success. Then, figure out a way to retrieve the data from the table with a query. Then, figure out a way do display the query results within the form.

I would store your incremented number in the table, without the leading zeros. You should be concentrating on writing values to a table. Concentrate on writing the incremented number to the table as data type Number (long integer).

Afterwards, you can create your alphanumeric thing in another field (within a table). With the alphanumeric thing complete, you can Format as needed using a query and or a control in a form.