Dont know why this wont work?

[Sam Summers]

Hi,

This should be really simple but doesn't work at all?

he only thing I can think of is that I renamed this textbox the same as a combobox that the code was based on before and it did work then.
So maybe it has somehow confused Access?

Option Compare Database
Option Explicit

Private Sub ClientName_Change()
     
    If Me.ClientName.Text = "TRA" Then
    Forms!Main!TOSubSection_Label.Visible = True
    Forms!Main!TOSubSection.Visible = True
    Forms!Main!ViewDROPSSubSectionBtn.Visible = False
    Forms!Main!ViewBySubSectionBtn.Visible = True
    Forms!Main!DROPSSubSection_Label.Visible = False
    Forms!Main!DROPSSubSection.Visible = False
    Else
    Forms!Main!DROPSSubSection_Label.Visible = True
    Forms!Main!DROPSSubSection.Visible = True
    Forms!Main!ViewBySubSectionBtn.Visible = False
    Forms!Main!ViewDROPSSubSectionBtn.Visible = True
    Forms!Main!TOSubSection_Label.Visible = False
    Forms!Main!TOSubSection.Visible = False
    End If

End Sub

 

[Minty]

You have to create form event properties from the builder in design view, as Access keeps track of them in a mysterious and dark way.
Copy the main section of your code, and paste it somewhere for storage. delete the whole code. Then rebuild the code from design view of the form, and paste the main section back in.

 

[Sam Summers]

I deleted the textbox and associated code,

Then I created a new textbox in design view and reposted the code in the 'On Change' Event - still nothing?

Do you mean to delete the entire code for the main form and start again?

 

[Minty]

This isn't correct

 If Me.ClientName.Text = "TRA" Then

If you simply want the value it's Me.ClientName

You can test for values you are looking for by using Debug.Print Me.ClientName in your code and seeing what it presents to you in the immediate window in the code window, in your case try

Debug.Print Me.ClientName ; Me.ClientName.Text

and you will see the differences in what you were using in your comparison.

 

[Sam Summers]

This is making me ill!!!

Its such a simple thing that worked before when I had a combobox that the user manually selected the client name from and then the necessary controls were visible and hidden as required but now that I have removed the combobox and applied the code based on the textbox it wont work?

I am near to giving the DB up as a lost cause?

 

[Minty]

I would suggest you take a step back, have a coffee and then concentrate on one issue at a time.

Currently you seem to be bouncing around at least 2 different problems, and it appears to me that you have now got the same issue in the other thread that you had some time ago.

Pick one thread - post up the current code and error message and/or post up the database with a simple description of what you are trying to achieve. Sometimes a picture with pointy arrows and a "i want this here" box, can be worth a thousand words.

Once the first issue is sorted - we'll move on to the second one.

 

[Sam Summers]

Its so annoying because its not even anything complicated?

I am going to take a look at how I achieved similar in a previous system I built?

This current one I am working on is currently 70mb so I think its too big to post?

Baffled though as to why I cant simply make something visible or not based on text in a textbox that I can see is there?

 

[Minty]

What was the result of the Debug.Print ?

 

[Sam Summers]

I may have put it in the wrong place?

I am getting nothing?

 

[Minty]

Okay - I'm going to ignore your other thread for the time being as I think you have a possible design issue, that might need a bit more work.

To try and fix this problem here is the modified code you should need.

Private Sub ClientName_Change()

    Dim sMsg As String
    
    sMsg = "This is what Me.ClientName has in it; " & Me.ClientName & " . " & vbCrLf 
    sMsg = sMsg & "This is what Me.ClientName.Text has in it; "  & Me.ClientName.Text & " . " & vbCrLf 
    sMsg = sMsg & "Was that what you were expecting? ! "
    MsgBox sMsg
    
    Debug.Print  Me.ClientName & " . " & Me.ClientName.Text & " . "  [COLOR="Green"]' This is for you to see the in the immediate window[/COLOR]
    
    exit SUB[COLOR="green"] ' Remove this once you have seen the problem[/COLOR]
    
    If Me.ClientName.Text = "TRA" Then
    Forms!Main!TOSubSection_Label.Visible = True
    Forms!Main!TOSubSection.Visible = True
    Forms!Main!ViewDROPSSubSectionBtn.Visible = False
    Forms!Main!ViewBySubSectionBtn.Visible = True
    Forms!Main!DROPSSubSection_Label.Visible = False
    Forms!Main!DROPSSubSection.Visible = False
    Else
    Forms!Main!DROPSSubSection_Label.Visible = True
    Forms!Main!DROPSSubSection.Visible = True
    Forms!Main!ViewBySubSectionBtn.Visible = False
    Forms!Main!ViewDROPSSubSectionBtn.Visible = True
    Forms!Main!TOSubSection_Label.Visible = False
    Forms!Main!TOSubSection.Visible = False
    End If

 

[Sam Summers]

I really appreciate your help!! Thank you

Nothing happened at all?

I wonder if I can shed some light on the process:-

When the user opens the database, a pop up, modal form comes up 'SelectReportFrm' where the user selects a report from a combobox which sets the Main form Textbox using this code

Me.ReportNumber.SetFocus
Forms!Main!ReportNo.SetFocus
Forms!Main!ReportNo.Text = Me.ReportNumber.Text

The textbox 'ClientName' on the main forms control source is =Mid([ReportNo],8,3)

Report number example - HE-182-TRA-DR-022

 

[Minty]

Not being difficult here but I think you need to understand a bit better what you are doing and referring to with your code. I'm going to go through a few things - please don't take offence if you think I'm being a bit Access 101 here but I am trying to assist.

1. I have asked why you are setting focus to different controls - it has no effect on what your code does afterwards unless you are performing a find record.

Which is why this

Me.ReportNumber.SetFocus
Forms!Main!ReportNo.SetFocus

makes no sense to me . You are going to one control then immediately afterwards going to another one.
What is your understanding of what that is supposed to be achieving?

2. I don't think you fully understand what you are trying to set or read from your forms; Lets say your have a combo box that has 2 columns. We'll call it cmbDropDown
Column1 = TableID , Column2 = TextDescription of TableID

Column1 is hidden from the user, all they see is column 2, as it is the text they expect to see.
This is normal practice to make the user interface easier to work with. However, normally you would store and use the ID field NOT the text.

If you look at the picture attached you will see two versions of the combo box the top one is what is actually used as far as the database is concerned, the bottom one is what the user sees.
If we were to use the following code based on the cmbDropDown we mentioned above

Dim strResult as String
Dim intResult as Integer

strResult = Me.cmbDropDown[COLOR="Red"].Text[/COLOR]
	[COLOR="Green"]'Then sResult would be "Service Call" [/COLOR]
intResult = Me.cmbDropDown[COLOR="red"].Text[/COLOR]
	[COLOR="green"]'Then sResult would be a type mismatch error as the text isn't an integer value
[/COLOR]

strResult = Me.cmbDropDown
	[COLOR="green"]'Then sResult will be the string "2" [/COLOR]
intResult = Me.cmbDropDown
	[COLOR="green"]'Then iResult would the number 2 [/COLOR]

Adding .Value to the last two statements will not change anything, it's the same thing.

I don't understand why the previous code I posted up would do nothing at all - it sounds as if it's not being called at all.
If you can make a copy of your database - remove 99% of the records , compact and repair the copy and then zip it to see if you can get the size down, then post it.

 

[Sam Summers]

Hi Minty,

Your help is hugely appreciated so no problem.

I'll try and answer your questions as clearly as possible:-

1. On the SetFocus thing, I was trying to respond to error messages saying that I couldn't reference the object unless it had the focus - that's all. So maybe I was chasing a problem?

2. Do you mean the 'ReportNumber' combobox? My problem is that report numbers are generated every time a new report is created but when the user opens the DB they select the report they are working on and depending on the 3 critical letters in a portion of the generated string (Report number) depends on what buttons etc. are visible due to the difference in reporting format required by the client. A bit of a nightmare but it has to be this way.

I will try and reduce the size of the DB and post it but not sure if I can but there are only 6 records in it at the moment?

 

[Minty]

Wow you must have severe database "Bloat" if that size you reported earlier is correct. I have a db with about 100 + forms, shed loads of queries, reports and code and it's only 45Mb !
Try a compact and repair then check the size, before opening it.

 

[Sam Summers]

Here is my zipped DB

It has attached images as part of the report

 

[Sam Summers]

I am changing/re-designing my forms for entering info (all of them to a form with the report number on with a subform to record the details which I think is where I have been going wrong?

The new form is not in the zipped DB I have posted.

 

[Minty]

Okay - I'll have a look and see what I can do. I am pretty busy with the paying day job at the moment , but will try and come back to you as soon as I can.

 

[Minty]

I am changing/re-designing my forms for entering info (all of them to a form with the report number on with a subform to record the details which I think is where I have been going wrong?

The new form is not in the zipped DB I have posted.

This sounds a positive step.

 

[Sam Summers]

No problem.
Thanks very much. Just remember its still work in progress!

 

[Minty]

Right I think I know what quite a lot of your issues are;

1st - The code you have attached to Private Sub ClientName_Change() Will work perfectly, but - And it's a very important BUT .... it won't get fired by you setting the value of ClientName via VBA code or macro's

This is why code that did work when it was a combo box now fails to.

Updates to any controls made via code do not trigger any further events to happen.

2nd. The code with the setfocus problems

'Me.ReportNumber.SetFocus      [COLOR="Green"]' No longer required[/COLOR]
'Forms!Main!ReportNo.SetFocus     [COLOR="green"]' No longer required[/COLOR]
Forms!Main!ReportNo = Me.ReportNumber.Text [COLOR="green"]'I've removed the .Text[/COLOR]

By referring to Forms!Main!ReportNo.Text in your code you force Access to deal with the control IN the form. By removing .Text you can set the controls value without needing to set focus. This is what I was getting at in my long post earlier, about understanding what object property you are referring to.

I'll show you how to trigger the code for your button hiding later, but hopefully this will get you started down solving some of your problems.