Forms not working correctly after changing data type to number

[MunroeM]

Hello



I have included a slimmed down version of my database in case you need to see for yourself what I'm about to explain.

I have 3 tables and 3 forms (U/S, Temmis, and deployed) all three tables and forms are basically the same except for wording and tag numbers. They were all working as intended untill i noticed that the tag numbers were sorting in a fasion i did not like. it was going from 1000's to 1100's to 1200's and then 300's (1000... ,1100...,1200..., 300..) so I changed the data type from Text to Number of the field USTagNum in USTagT. It is now sorting how i would like (300..,1000..,1100...,1200...) But now there is a new issue. I verified by changing the datatype from text to number in the TemmisTagT and the problem is now happening on the temmis form also.

I will describe how the TEMMIS form should work normally and just remember that the other two forms work the same way. When you select the Sign in button Temmis Tag In combo box becomes usable. you then select the tag number you are signing back in and it will populate the boxes below with the info for that tag.

Now after changing the datatype to number you select sign in and then select a tag number and no info shows up in the boxes below and if you change the tag number you selected three times a microsoft access box pops up saying "Update or Cancel update without AddNew or Edit."

I could just change the datatype back to text but then my sorting will be all off again so i was wondering if there is a way to get it working the way it used to but still keeping the datatype as number. Or if you know of another way to sort so its will not come out 1000, 1100, 1200, 300.

If you want to play around with the database i uploaded then the Deployed form is the one that is still working the way i want it to. The Temmis and US are not working

Thanks

[June7]

As text, the values would need place holder leading zeros to properly sort: 0001, 0300, 1000, 2000, etc.

You can construct a field in query with an expression: Format([DeployedTagNum], "0000")

Now use that field for sorting and filtering.


Note misspelled 'Dectription' in user instructions on your form.

[MunroeM]

Thanks for picking up the spelling error.

Now if i us the Format([DeployedTagNum], "0000") is that going to change the way the numbers will show up in the combo box? Will tag 30 show up as 0030 in the drop down box?

[Missinglinq]

June has explained how to get the sorting you want, with the Field being defined as Text.

But FYI, the reason for the problem, when changing the Field to a Number Datatype, is how different Datatypes are delimited. Below is typical code for retrieving a Record using a Combobox.

If the identifying Field is defined as Text, as yours was:

Private Sub ComboName_AfterUpdate()
  
 Dim rs As Object

 Set rs = Me.Recordset.Clone
 rs.FindFirst "[ID] = '" & Me![ComboName] & "'"
 If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

If defined as a Number:

Private Sub ComboName_AfterUpdate()
    
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Me![ ComboName]
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

The same general syntax is used elsewhere for 'where' clauses and 'criteria' clauses.

Linq ;0)>

[MunroeM]

As text, the values would need place holder leading zeros to properly sort: 0001, 0300, 1000, 2000, etc.

You can construct a field in query with an expression: Format([DeployedTagNum], "0000")

Now use that field for sorting and filtering.

How would I go about doing this? I still have very basic knowledge in access.

[June7]

It can show as 0030 if you want it to. If you build the combobox RowSource sql with the calculated field.

What do you not understand? How to create a field in query with expression? Basic Access functionality, review http://ms-access-tips.blogspot.com/2...n-queries.html

[MunroeM]

I would prefer that they only show 30 from 0030 or 340 from 0340.

I will have a read over that link you posted and see if it helps me understand queries better.

Thanks

[June7]

But do you want the combobox values to sort properly? Try RowSource:

SELECT DeployedTagNum FROM DeployedTagT ORDER BY Format([DeployedTagNum], "0000");

or

SELECT DeployedTagNum FROM DeployedTagT ORDER BY Val([DeployedTagNum]);

[MunroeM]

But do you want the combobox values to sort properly? Try RowSource:

SELECT DeployedTagNum FROM DeployedTagT ORDER BY Format([DeployedTagNum], "0000");

or

SELECT DeployedTagNum FROM DeployedTagT ORDER BY Val([DeployedTagNum]);

That link you sent me worked well. I did not know you could enter all that in the field section.

Both of the options you gave me worked. But the Format one did not work in the end as i use an append query to copy the record to a history table and it prevented the append query from working. But the Val one works like a charm

Thanks again for helping me out

Munroe