Refresh takes care of this problem? That's kind of like
smacking your TV to clear up a snowy picture (sometimes it
works, but no one knows why). Refresh's purpose is to
update existing records with any changes made by other
users. It will save any changes to the current record and
repaint the screen, but I thought doing an explicit Repaint
would be more direct and efficient. Who knows, maybe saving
the current record was the key???
Me is used to refer to the form/report containing the VBA
line of code where it is used. In many situations, it is
not absolutely necessary, but it does disambiguate a
control/field and VBA variable with the same name. Using
Me. allows for a more meaningful error message when you
misspell a name. Using Me. also provides you with the
IntelliSense selection list of legal things that can be
used, while Me! does not. There are a couple of other
subtle differences, but Me. and Me! are effectively
interchangeable **when followed by a control name or a
record source field name** In a broader context, dot is
used to refer to an object's properties and methods (e.g.
textbox.Visible). Bang (!) is used to refer to members of a
collection (e.g. Forms!myform). In a query, you always use
a dot between a table name and a field name. Bang is not
used in SQL syntax.
As long as the issue is resolved, I guess we just have to
say whatever works . . .
--
Marsh
MVP [MS Access]
I ended up putting an Me.Refresh command in subroutine and that seems to be
working!
I have a few VB questions to help understand what I have just written:
1. What does Me. mean?
2. When do I use "." and "!" between the field and table?
I have learned a lot through this. Thank you so much!
Irene said:
Below is a sum total of all of the things that I have tried in the program.
I am still having a problem with only Layers updating when the program is
selected. The other two fields only update after another record is viewed or
the window is minimized and restored.
Is there something else I can do? Maybe put a button on form that causes it
to update? Maybe put this code into a different event, such as "On Click"?
Thank you so much for your patient support!
Private Sub ProgramCombo_AfterUpdate()
'Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
' strFilter = "Program = """ & Me!Program & """"
' Look up product's unit price and assign it to UnitPrice control.
'Me!TotalPasses = ProgramCombo.Column(1)
'Me!FiberArea = ProgramCombo.Column(3)
'Me!Layers = ProgramCombo.Column(2)
Me.[TotalPasses] = ProgramCombo.Column(1)
DoEvents
Me.Repaint
Me.[Layers] = ProgramCombo.Column(2)
DoEvents
Me.Repaint
Me.[FiberArea] = ProgramCombo.Column(3)
DoEvents
Me.Repaint
End Sub
Marshall Barton said:
OK, I thought these values were just for display purposes,
but if they can be edited, then we have to go back to using
the AfterUpdate procedure. With this approach, the only
code that's needed is:
Me.TotalPasses = ProgramCombo.Column(1)
Me.Layers = ProgramCombo.Column(2)
Me.FiberArea = ProgramCombo.Column(3)
--
Marsh
MVP [MS Access]
Irene wrote:
When I do this, the values do show up in the form right after the program is
selected, however I need to store the values in the table and if I put in the
TotalPasses
=ProgramCombo.Column(1)
it just displays but does not store the value in TotalPasses. These values
are kind of recommended values, but I want the operator to be able to enter
the actual values used if they differ from these recommendations.
:
Very odd!
Let's try to find a way to eliminate the DLookups
altogether. Since the combo box and the DLookups are all
using the same record in the program table, you can modify
the combo box to retrieve all the needed values by changing
its RowSource query to:
SELECT Program, Passes, Layers, FAperWind
FROM [MCS Program Table]
and setting the combo boxes ColumnCount property to 4. Set
the ColumnWidths property to ;0;0;0 if you don't want to
see the extra columns in the drop down list.
With that taken care of, you can remove the combo box's
entire AfterUpdate event procedure and use these expressions
in the text boxes:
TotalPasses
=ProgramCombo.Column(1)
Layers
=ProgramCombo.Column(2)
FiberArea
=ProgramCombo.Column(3)
Irene wrote:
Yes, you are correct column count and bound column are both 1.
I pasted the complete code below. One of the text boxes actually does
update after selection (Layers), but the other two do not. You are right
that they will update after I minimize and restore the window. However,
adding the two lines at the end did not help. When I remove two of the three
DLOOPUP lines it still doesn't update on selection.
Private Sub ProgramCombo_AfterUpdate()
Dim strFilter As String
' Evaluate filter before it's passed to DLookup function.
strFilter = "Program = """ & Me!Program & """"
' Look up product's unit price and assign it to UnitPrice control.
Me!TotalPasses = DLookup("Passes", "MCS Program Table", strFilter)
Me!Layers = DLookup("Layers", "MCS Program Table", strFilter)
Me!FiberArea = DLookup("FAperWind", "MCS Program Table", strFilter)
DoEvents
Me.Repaint
End Sub
:
You now have bothe ColumnCount and BoundColumn set to 1,
right?
So, you're saying the value doesn't appear in the text box
until you navigate to any other record and baxk again. That
is a little strange and I don't think I've ever seen that
effect in such a simple situation. It sounds like Access is
either too busy or lost track of what's on the scren. If
I'm right about this, you should be able to get the value to
appear by doing some other funky thinks such as maximizing
the form, dragging some other application's window over the
form or maybe even by waiting a minute or two. You might
first try closing Access and restarting your computer to see
if this effect goes away. If that doesn't help, then try
adding either one or both of these lines to end of the event
procedure:
Me.Repaint
and/or
DoEvents
Irene wrote:
Program is text and my primary key. Should I have an indexed Program ID
instead?
MCS Program Table
Program Text
Passes Number
Wind Table (the form adds data to the wind table)
Program Text
TotalPasses Number
The rowsource for my ComboBox is:
SELECT [MCS Program Table].Program FROM [MCS Program Table];
I think the columncount may have been my problem.
Now the update works but I need to view another record and then go back to
the one I was editting for the data to be pulled up. How do I get the data
to appear right after I select the program from the combo list?
Thank you.
:
Are you sure that the combo box's BoundColumn property
corresponds to the Program field? It's common for the bound
column to be a key field rather than the name of a program.
Also double check the ColumnCount property to make sure it
exactly matches the number of fields in the row source
table/query.
In order to unravel this sort of thing, we need to look at
both the combo box's RowSource table/query and the MCS
Program Table so we can see how the fields in each
table/query relate to each other. If the combo box's
RowSource is a query, please post a Copy/Paste of the
query's SQL statement. Along with that please list the
relevant fields and their data type.
Irene wrote:
Thank you very much. That really clears up some syntax related problems I
was having.
Now the error no longer appears, but I am not getting the result I wanted.
I was trying to copy a piece of code from the NorthWind Database. Once a
Program is selected from a drop down menu in a form, I wanted the TotalPasses
text box to be filled in automatically from information in the MCS Program
Table. The code that I pasted was from the After Update event in the combo
box. This is similar to the Orders Form Unit price appearing when the
product is selected.
Any suggestions as to where I have gone wrong? Obviously I have very little
experience in VB programming.
:
Irene wrote:
The following piece of code gives a Missing Operator Error on the 4th line.
I am new to VB programming. What does this error mean? I have searched the
help file and have not be able to find the answer.
Private Sub Program_AfterUpdate()
Dim strFilter As String
strFilter = "Program = " & Me!Program
Me!TotalPasses = DLookup("Passes", "MCS Program Table", strFilter)
Exit_Sub_Program_AfterUpdate:
Exit Sub
End Sub
That probably menas that Program is a Text field. If it is,
then you need to enclose the value in quotes. To get quotes
around the value in the filter you need to use:
strFilter = "Program = """ & Me!Program & """"
The reason for all those quotes is because you need to use
two quote inside a quoted string to end up with one quote.
I.e. """" is how you add " to the end of the string.
