Access 2010 Query or Event Procedure Issue?

[justphilip2003]

When in my Form Gallery, I makea selection in the comboBox, Select_CrNr the selected value has changed by thetime the Private Sub Select_CrNr_AfterUpdate() Event procedure for the ComboBox is entered(proved through Debug Toggle Breaks and MsgBox displays) and the Form display of selected value. I suspect my problemlies with the query since the incorrect value changes befor entering the event procedure but I can’t figure it out. Below is the code in use. TIAPhil

Query:Find_CrNr_Gallery identified in Form Record Source and
FieldID_Carv of the Query has criteria: Like [Forms]![Gallery].[Select_CrNr] &"*"

CarvingTable fields of concern: ID_Carv, CarvingNR, …,JPEG_Id occur in this order; CarvingNR and JPEG_Id always match but are the wrong values.

Private SubSelect_CrNr_AfterUpdate() ' <=========== first line combobox entry
Dim Ver As ‘ <== not in play yet
Dim CalVer As Integer ‘ <== not in play yet
CalVer = 1 ‘ <== not in play yet
MsgBox "Select_CrNr value enteringComboBox Sub = " & Me.Select_CrNr
Me.Requery
JPEG_Hold = JPEG_Id
MsgBox "Select_CrNr value enteringComboBox Sub = " & Me.Select_CrNr
Sr_Message = "**" & JPEG_Hold& "**"
Call JPEG_Display
End Sub



Form GalleryComboBox Select_CrNr Property Sheet Row Source:
SELECT[Carving Table].IDCarv, [Carving Table].CarvingNR FROM [Carving Table] WHERE((([Carving Table].IDCarv) Like [Forms]![Gallery].[Select_CrNr] &"*")) ORDER BY [Carving Table].CarvingNR;

Form Gallery ComboBox Select_CrNr Property Sheet Row SourceType: Table/query

[Robeen]

Hi Phil! Not sure if it's just me, but would it be possible for you to just paraphrase the problem without throwing in the code. Just a very brief, short-sentence summary of the issue you are having might help.

[ssanfu]

Looking at your code, of course the PK will change!!

First, take out the this line>>> Me.requery

The query for the combo box "Select_CrNr" has a WHERE clause that is selecting the "IDCarv" LIKE the combo box "Select_CrNr*". The IDCarv number changes, so the row source is requeried. A circular loop.

Is "IDCarv" a text type field in the table or a Long integer?

Try this as the combo box row source:
SELECT[Carving Table].IDCarv, [Carving Table].CarvingNR FROM [Carving Table] ORDER BY [Carving Table].CarvingNR;

[justphilip2003]

First thank you for the responses. I implemented ssanfu suggested changes (removed Me.Query, and changed the Select code) and what I ended up with was consistant with pre change tests: I selected the combobox value of "58", the msgbox messages show "1" as being selected not "58" and record "103" was sought. In a word, total chaos!Record "103" does not exist, hence the error routine kick in. "IDCarv" is the table primary key. In the Table, CarvingNR "58" has a primary key of "1". Could my code be returning PK instead of tghe desired CarvingNR? TIA

[justphilip2003]

Another test I select the combobox value "43", the msgbox showed "45" and "103" was sought. CarvingNR "43" has a primary key of "45". Why is the PK being returned as the as the combobox value selected? TIA Phil

[ssanfu]

Another test I select the combobox value "43", the msgbox showed "45" and "103" was sought. CarvingNR "43" has a primary key of "45". Why is the PK being returned as the as the combobox value selected?

Usually, the PK field is the bound column - but you can set the bound column to any column you wish.


If the row source is

SELECT[Carving  Table].IDCarv, [Carving Table].CarvingNR FROM [Carving Table] ORDER BY [Carving Table].CarvingNR;

and the combo box has these settings

(on Data tab)
Bound column = 1

(on Format tab)
Column count = 2
Column widths = 0";1 (this sets the first field, "IDCarv", to not visible and the 2nd field, "CarvingNR", to visible)

Then the "IDCarv" (PK?) is being returned.
Try adding this line to the code:

MsgBox "IDCarv value  = " & Me.Select_CrNr & newline & newline & "CarvingNR value  = " & Me.Select_CrNr.Column(1)

Note the code in red.

[justphilip2003]

Okay, the new code displays the orginal combobox value, but how do I get the field CarvingNR returned instead of IDCarv. The query was intended to match combobox value to the record containing a CarvingNR value equal to the combobox value? Should I consider changing Row source tyoe to a "field list" instead of a table query? TIA Phil

[ssanfu]

Kind of blind here.... No database... only have seen code from combo box. Don't know what the table structure/relationship looks like, no examples of what you have and what you want returned.


Did you try the message box code? What was the result?

Private Sub Select_CrNr_AfterUpdate()
' other code
MsgBox "IDCarv value  = " & Me.Select_CrNr & newline & newline & "CarvingNR value  = " & Me.Select_CrNr.Column(1)
'other code
End Sub

Okay, the new code displays the orginal combobox value, but how do I get the field CarvingNR returned instead of IDCarv.

1) "Me.Select_CrNr.Column(1)" will return the "CarvingNR" value if the row source is "SELECT[Carving Table].IDCarv, [Carving Table].CarvingNR FROM [Carving Table] ORDER BY [Carving Table].CarvingNR;"

2) Change the row source to "SELECT [Carving Table].CarvingNR FROM [Carving Table] ORDER BY [Carving Table].CarvingNR;" . Change the "Column width" property to 1 or delete the value.

[justphilip2003]

I think your code is getting me closer to the final solution! The fact that all combobox entries end up seeking the record 103 leads me think that the criteria code {Like[Forms]![Gallery].[Select_CrNr] & "*"} in the query is searching for all records that meet the criteria and ends the search with record CarvingNR 103. I think the criteria needs to be changed to FindFirst but I do not know how the syntax should look like. Any suggestions? TiA Phil

[ssanfu]

Form GalleryComboBox Select_CrNr Property Sheet Row Source:
SELECT[Carving Table].IDCarv, [Carving Table].CarvingNR FROM [Carving Table] WHERE((([Carving Table].IDCarv) Like [Forms]![Gallery].[Select_CrNr] &"*")) ORDER BY [Carving Table].CarvingNR;

I've been re-reading your first post(with the code) and I'm still not sure what you are trying to do. Normally a combo box row source doesn't reference itself.
It kinda looks like you are trying to set up cascading combo boxes using one combo box instead of two combo boxes.

What is the field type of "IDCarv"?
What is the field type of "CarvingNR"?

Does the field "CarvingNR" have duplicates? (for example Is there more than 1 record where the "CarvingNR" field = 43?)

Would you post a representative number of records from the table "Carving Table" as a CSV/TXT file? (for testing)

So, what are you trying to do????

[June7]

This issue also discussed in thread https://www.accessforums.net/forms/f...tml#post168457

I will no longer respond to the other thread and hope solution can be found in discussion here.

[justphilip2003]

Carving Table.txt The attached text file contains the Carving Table.

[justphilip2003]

This project was to be a simple search of the Carving Table through the use of a query and form combobox so I could display a particuliar picture of my carving. The query recieves the selected combobox value but always returns the record associated with carving 103; this fact disturbes me the most. IDCarv is the PK, CarvingNR is unigue long number (no duplicates), StockID is the primary key to another table, the next three fields are text and the last is a memo field. The combobox Row Source is set to "SELECT [Carving Table].CarvingNR FROM [Carving Table] ORDER BY [Carving Table].CarvingNR;" and the query criteria is set to {Like [Forms]![Gallery].[Select_CrNr] & "*"}. TIA Phil

[June7]

I don't know about ssanfu but if you want any more assistance from me will have to provide database for analysis.

[justphilip2003]

I will try again to upload the db. Phil