User defined field control/propertiew?

[justphilip2003]

I feel ashamed to post this question, but I have forgotten so much.

I have a very simple Form with a combo box ("Stage" Property Name), a text box ("Select_Nr" Property Name) and fields that are Table defined. I am using Access 2010.

The intent is take the values stage and Select_Nr, manipulate them to access the Table and display the desired fields. I am experiencing an "Invalid Control Property: Control Source" and "No such field in the field list" error messages with the text field Select_Nr. The text box does not accept entries. I feel I need a control statement(s) to define the properties but where to put them; the code in Event Procedure [Private Sub Select_Nr_Click()] never executes! :banghead:

 

[justphilip2003]

While reviewing my VBA code, I noticed another list of properties dor the errant field box Select_Nr. I changed entry ControlType from a 110 to a 109 and I am now able to enter data but the associated Event Procedure does not execute.:banghead:

 

[CJ_London]

Sounds like you are trying to modify forms through modifying the form properties?

If so, please can you post the code that creates or modifies the form

 

[justphilip2003]

There is no intent in the Form logic to self modify. I was in a debugging mode and happen to see the Controltype entry and did a comparison to a newly constructed Textbox. The following is the event procedure that does not execute:

Private Sub Select_Nr_Click()
' Ctl = Select_Nr
Debug.Print "Start of Procedure"

On Error GoTo Err_Select_Nr_Click

Dim Filename As String, pathname As String
Dim Select_Nr As Integer
' Dim Select_Nr As String
Dim Ver As Integer
Dim BldFileNm As String
Dim FileLocaton As String
Ver = 1 ' Form incomplete for Ver logic
' Fetch the relative bath for DBM
pathname = CurrentProject.Path

' Enter Select_Nr set use it
' to construct file name
' the field "stage" contains the front end of file name
' use Select_Nr to build middle of filename
' file name format is CrNr0058v1.jpg for Yoda
'
' Determine Select_Nr range: units, tens or hundreds
If [Select_Nr] < 10 Then
NumSize = "00"
BldFileNm = [Stage] & NumSize & [Select_Nr] & "v" & Ver & ".jpg"
Else
If [Select_Nr] < 99 Then
NumSize = "0"
BldFileNm = [Stage] & NumSize & [Select_Nr] & "v" & Ver & ".jpg"
End If
BldFileNm = [Stage] & [Select_Nr] & "v" & Ver & ".jpg"
End If
' report progress
Debug.Print "BldFileNm = ", BldFileNm
' Construcrt file name
' Build full file path
Filename = pathname & "\" & BldFileNm

' Me.ImgStock.Picture = Filename
'Me.Ffilename = Filename

GoTo Exit_Select_Nr_Click
Err_Select_Nr_Click:
MsgBox Err.Description
Resume Exit_Select_Nr_Click
Exit_Select_Nr_Click:
End Sub


++++++++++++++++++
I feel that the field Select_Nr must needs control defining code, probably in the Form, but what and where.

Thanks for taking an interest.

 

[CJ_London]

Have you tried removing the square brackets around Select_Nr

Also be helpful to put code tags around your code which is standard practice for this forum (i've been told off for not doing it! - you need to construct your reply using the advanced method to see how to do it

BldFileNm = [Stage] & NumSize & Select_Nr & "v" & Ver & ".jpg"
 
If Select_Nr < 10 Then
 
etc

 

[justphilip2003]

I removed the [] no change. The procedure may be executing (?) but if it is, it is not recognizing the screen input. I added text boxes to display Pathname and filename - they also have an error comment "Invalid Control Property: control source" and "No such field in the field list". last time I had these messages I changed the ControlType from 110 to 109; however they were already set to 109. Puzzled!

 

[CJ_London]

I'm running out of ideas

However I've just noticed you have Select_Nr defined in your procedure with Dim - I thought it was a form control? If not, you aren't assigning a value to it so it will be empty. If it is your form control, you need to remove the dim statement.

I also found this link which may be helpful

http://office.microsoft.com/en-gb/access-help/no-such-field-in-the-field-list-HP006361311.aspx

The only other thing I can suggest if you haven't tried it yet is to try debugging the code from the start of the function and see what else turns up.

Not sure if you know how to do this, but click in the grey vertical column to the left of the vba code and a maroon dot appears. When you run your code it will stop here, highlighting the line in yellow. Pressing F8 will move you on a line. Hovering over a variable will display its value, alternatively add in debug.print VariableName after it has been assigned a value and it will appear in the immediate window once the line has executed.

 

[boblarson]

A couple of ideas (not sure which version you have):

1. Have you enabled code (set a trusted location or clicked the button to enable code)?

2. When you changed the control type, how did you do it?

3. When you changed the control type, did you remember to go to the event properties window and select [EVENT PROCEDURE] for the click event of that particular control?

 

[justphilip2003]

On using the debug feature with dot, the expected code never executed.

No special button to cause execution is in place, I was thinking the {Private Sub Select_Nr_Click()} would cause execution.

I effected the control type error removal by removing the field name in control source of the property sheets. I did activate the event procedure in the property sheet.

I have an earlier study that works (brute force) without any screen input - bad example, it uses a Table containing hard coded file names which call up JEPG images.

I am also beginning to think code developed for other failed attempts are being accessed. Is there a way to privatize the intended event procedure to this Form only? TIA

 

[CJ_London]

you need a control called Select_Nr on your form to cause Private Sub Select_Nr_Click() to run

Recommend you remove the dim and set a control called Select_Nr on your form

 

[justphilip2003]

Thanks for the suggestion. This coding problem is a new experience ( all previous DBMs had explicit Table Fields associations. I do not know how to code this control. My stand alone Form name is Gallery, it contains a "Private Sub Form_Current()" procedure. I believe the code belongs in it or is there an appropriate entry in the Gallery Property sheet?

 

[CJ_London]

So how does Select_Nr get assigned a value for

If Select_Nr < 10 Then

to work?

 

[justphilip2003]

I am really stuck back at the Form level of establishing the event Procedure Select_Nr_Click(); but to answer your question: (1) a text box Select_Nr can have a value in this range, 999 < Select_Nr < 0, (2) since Select Nr is defined as an integer, I need to know how many zeros to place before it in a text string format, (3) the stage field can have one of two values BcNr (beginners carvings) or CrNr (intermediate cataloged carvings), (4) there are more than one view for some carvings in the Picture folder hence the Ver field to display the versions beyond 1, (5) the constructed Filename is used to access the pictures once I get beyond the control block.

Thanks for your patience.

 

[CJ_London]

I'm sorry,

I do not have enough information to advise further. The only other thing I can suggest is if you can upload a sample of your db with some data and the form you are trying to get to work

 

[justphilip2003]

Gallery is a stand alone Form for displaying pictures. The associated Tables will change and are only describe specifics for the carving. The Picture folder can be reduce to a few images. How do I export Gallery and Table and accomplish an upload? TIA

 

[boblarson]

Gallery is a stand alone Form for displaying pictures. The associated Tables will change and are only describe specifics for the carving. The Picture folder can be reduce to a few images. How do I export Gallery and Table and accomplish an upload? TIA

How to Upload a Database to the Forum

 

[justphilip2003]

Re: User defined field control/properties?

I hope the zip file works. New computer forced me to purchase WinZip and then muddle my way through using it, labels and tutorials do not match Win8 ribbon. I had to mail it to myself first! The DBM is small. I included sever picture that need to be in a folder Gallery_Pics to match my coding. TIA

 

[boblarson]

Re: User defined field control/properties?

I hope the zip file works. New computer forced me to purchase WinZip and then muddle my way through using it, labels and tutorials do not match Win8 ribbon. I had to mail it to myself first! The DBM is small. I included sever picture that need to be in a folder Gallery_Pics to match my coding. TIA

I don't have Win8 so I didn't realize that it no longer lets you unzip files without a zip utility. In Windows Vista and Windows 7 you could just right click on the file and select SEND TO > COMPRESSED FOLDER.

 

[justphilip2003]

I can't say Win 8 doesn't zip, I can say I do not know how it would be done. It's been a real struggle to going from Vista up to win 8 and
Office 2003 up to office 2012 while re-establishing my other favorites all on a new PC. All that after being away from Access/VBA coding for about 8 years. I feel retarded! LOL