Recordset not showing alpha-numeric entries

[rayhawk]

I have a form that I use to list each part (by part number) and show the relevant information for that part. I have everything working as long as the part number begins with a number. However, if the part number begins with a letter, it does not show up in the recordset sort for the form. The part and all the data is entered into the table correctly, but when I re-open the form, any entries that begin with a letter don't show up at all. My part number field is set to text, and I don't understand why Access wouldn't show every entry in the table?



I don't even know why the part number matters at all, it should be treated like any other entry to the table. My primary key is not the part number, it is an autonumber field.

[rayhawk]

I get the following error when I added a new alpha-numeric part number (where the letters are in the middle of the part number). This has to do with my lookup routine, this may point to the problem.

I can't post an image, but the error says:

The object doesn't contain the Automation object 'cmbpmalookup.text.'

You tried to run a Visual Basic procedure to set a property or method for an object. However, the component doesn't make the property or method available for Automation operations.

Check the component's documentation for information on the properties and methods it makes available for Automation operations.

[michaeljohnh]

Are you using a combo box to select the part number on the form? If so, I'd like to see the row source SQL. There may be something in it unintentionally filtering out alpha characters.

[rayhawk]

Are you using a combo box to select the part number on the form? If so, I'd like to see the row source SQL. There may be something in it unintentionally filtering out alpha characters.

It is just a text box, that is what has got me so confused. And I should specify that for Part Numbers where the alpha characters are at the end, they show up fine. It is when the alpha characters are at the beginning or in the middle of the part number that it doesn't show up.

I also have a part number search function, that shows all the items correctly, but when I select one with the alpha characters at the beginning, it can't find the record.

This is my search routine:

Private Sub cmbPMAlookup_AfterUpdate()
    Form.Recordset.FindFirst "Projects." + "[pmaPartNumber]='" + cmbPMAlookup.Text + "'"
End Sub

[michaeljohnh]

Any chance you could post the database? At least the relevant portion of it?

[rayhawk]

I trimmed all but 2 part numbers, one that begins with alpha characters and one that doesn't. If you look at the lookup function or at the projects table you will see the part number is in the table, but does not show as a record.

[rayhawk]

Just wanted to give an update, and thanks to those for looking at my issue. It turned out not to be an alpha-character issue at all. It had to do with how the form was loading the data for the recordset. One of my relationships required a drawing and revision level to be set, or it wouldn't show up in the recordset. I just had to go through and either set a revision level, or because I didn't always need that, I changed the relationship to include all drawings (and ultimately part numbers) in the recordset even if there was no revision level.

[RuralGuy]

I take it you are ready to follow the link in my sig and mark this thread as Solved?

[rayhawk]

I take it you are ready to follow the link in my sig and mark this thread as Solved?

Thanks, I was looking for that solved button.

[RuralGuy]

We aim to please.