Display incremented value in form

[scottdg]

I am trying to take a value from a text field in a table, take the 7 characters to the right and convert them to a number so I can then increment that number by one (for each record displayed on my form) and write it to my form. Since I am a novice I am not sure of the proper syntax for this.

In the code below I am first just trying to figure out how to properly reference the Log_SeqNo in the Val() function and display it in the text box in my form. But I am ultimately going to want to increment it by one. So if I take the text from the table which is 15-0001234. I want to strip the 0001234 and convert it to a number. I then want to increment that and write it to my form so that if I am displaying ten records it will display



Bob 15-0001235
Scott 15-0001236
Ted 15-0001237

I assume that once I convert it to a number it will strip off the lead zeros so I will need to add them back and the "15-".

Private Sub Form_Load()
SeqNo = Right(Val([Log_SeqNo]), 7)
txtLogNum = SeqNo
End Sub

Thanks,
Scott

[ItsMe]

Access should automatically parse the text "0001234" to a number type.

Dim MyInt as Long
Dim MyString as String

MyString = "0001234"
MyInt = Mystring
msgbox MyInt

[scottdg]

The text column in my table is "15-0001234" when I try to use the DMAX() function to find the greatest value and add 1 I am getting a type mismatch. Not sure how to get the Log_SeqNo from my TI_FI_LOG table so that I can add 1 to it ten times and display the ten incremented numbers in the form.

[ItsMe]

Maybe you can create an alias in a query using the Mid function.

I tested this in VBA

Dim str As String
str = "15-0001234"
str = Mid(str, InStrRev(str, "-") + 1)

You might be able to use DMax on the result if it was in a query.

So an alias might look like

MyAlias: Mid([FieldName], InStrRev([FieldName], "-") + 1)

[scottdg]

Maybe we can take a step back for a second...I apologize but still learning

The following code runs and I assume it is working properly but I cannot tell because as soon as I add the commented line at the bottom I get an error "Microsoft Access can't find field '|1' referred to in your expression."

Private Sub Form_Load()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("T_FI_LOG")

SeqNo = Right(Val([Log_SeqNo]), 7)

'txtLogNum = SeqNo

End Sub

[scottdg]

I take that back it is not running even with the line commented out.

[ItsMe]

The following is VBA you can test in a form. Maybe test behind a temporary control like a Command Button.

Dim str As String
str = "15-0001234"
str = Mid(str, InStrRev(str, "-") + 1)

What you have done here

Private Sub Form_Load()
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("T_FI_LOG")

SeqNo = Right(Val([Log_SeqNo]), 7)

'txtLogNum = SeqNo

End Sub

Is going to get you nowhere fast.

You need to parse a number from your text field. I suggest you start with that first. You can accomplish it within a Query Object. Create a query using the Query Designer, click "Query Design" from under the Design tab.

If you add your table to the Query Design Window, you can, then, add the desired fields to the grid at the bottom of the Design Window. You can create an alias for a field by adding the alias name and follow it with a colon. MyAlias:

Here is an alias with an expression that parses characters from a text field.

MyAlias: Mid([FieldName], InStrRev([FieldName], "-") + 1)

You need to put the correct field name in the red text areas.

[scottdg]

Thanks for the help, I appreciate it.

I parsed the string to get the number and converted it to a number using val(). I then incremented the number to 1235.

I have two other questions.

1. I am not sure of the syntax to make str equal to the Log_SeqNo column in my T_FI_LOG table instead of "15-0001234". This way I can increment it in the form based on the highest value in the table. Then the user can save the form data back to the table with the new incremented number.
2. How do I get it to increment for each row in my form? Right now it is 1235 for all ten rows.

Thanks.

[ItsMe]

...1. I am not sure of the syntax to make str equal to the Log_SeqNo column in my T_FI_LOG table instead of "15-0001234". This way I can increment it in the form based on the highest value in the table. Then the user can save the form data back to the table with the new incremented number...

My suggestion is to create a query and use an alias to determine the number part of your alphanumeric field. I believe post #7 explains how to approach this.

[scottdg]

The form I am using is already using another query as a record source. Can I have 2 record sources? I didn't think so. I need to display all the data for the customer in a row with the log number so I don't think a subform will work.

Let me give you a little more info on what I am trying to accomplish. I have two tables, one of them is linked to a SQL table and the other will be form data imported from an excel spreadsheet. The form/query in question is taking a random sampling of 10 customers from the linked table based on county and displaying some relevant information. I then want to save that data to the other table created from the import. Along with the customer data from the linked table I want to write the newly generated Log number which like I said above needs to be created by taking the highest value in the import table and then incrementing the log for each customer in the sampling of ten.

So I can create another query as you suggested but I am not sure I can use both queries as a record source for the form.

[ItsMe]

My suggestion is to create a query and use an alias to determine the number part of your alphanumeric field. If you already have a suitable query there will not be a need to create another one.

[scottdg]

Yes - that will allow me t parse the log number and display it as a number in that query. I am still not sure how I can use 2 queries as a record source in my form. Even if I join the tables in the query since I am taking a random sampling of 10 records it isn't as if I can use that to find the highest log number in the table.

[ItsMe]

I read post #10 several times. Actually, I read it many times. I think I see why you are worried about a second query and also why you were attempting to use VBA and DAO (in post 5).

I really like VBA and DAO. I use it a lot. DAO may be the way to go, at the end of the day. Also, it may be possible to use DMax instead of creating VBA and a DAO object.

Regardless, you will need to create an alias field that works as a column in a query. Try not to focus on your form and it's recordsource. If you were to create a DAO object using VBA, it would not have the slightest impact on your Form or its RecordSet or even its RecordSource (unless you told the DAO object to). Once you have the Query Object that contains the alias (it can be a totally separate query, it can be part of your form's recordset or not, it can be in a separate database for that matter. The idea is you create a query that retrieves the data you need. Then, create an alias (within the query object) that uses the expression to parse the number.

Once you have that alias, use DMax, use DAO, use SQL, use whatever to find the value you need to increment by one. Once you have found it, realize the Primary Key value of that record. Then, UPDATE whatever table, record, etc. you need to.

[scottdg]

I created a query that finds that max(Log_SeqNo) then I added that query to the original one my form is using for the rest of the data. I can now view that Log_SeqNo in my form (below). It is still a string. I added the function you gave me earlier to parse it and it is fine in the query but when I change my text box to now show the parsed value it says "#Name?" I am pretty close. All I need to do is convert it to a number increment it for each row and then add the "15-" back to it before writing the records to the T_FI_LOG table.

[ItsMe]

...I added the function you gave me earlier to parse it and it is fine in the query but when I change my text box to now show the parsed value it says "#Name?"...

What is the name of the alias in your query? I do not suspect you will need to view the parsed data in your Form. However, all you have to do is add a new control to your design surface from the Add Existing Fields list. Just locate the Alias name.

As for the numbering sequence. Perhaps a DMax on the alias name would work. You could do it from behind the form using VBA. This way, you can add 1 and UPDATE your table too.