Continuous form help?

  • Thread starter Thread starter Jeffrey K. Ries
  • Start date Start date
J

Jeffrey K. Ries

I have a subform which I am trying to perfect, but I am running into a
problem. I have two fields which push values upon each other. They are
'Project Name' and 'Project Number', the theory is that a user can choose
one or the other and the opposite will be automatically filled in. This
works fine through combo boxes with event procedures attached to the
OnChange and OnExit properties.

The problem is that if the focus is on one of these combo boxes then I
cannot select another record in the list. I first have to select another
coulmn in that record and then on another record.

Has this happened to anyone else? Is there a way around it?

Thanks,
Jeffrey K. Ries
 
Certainly.

The OnChange and OnExit code is the same. I find that I need both in order
to either use the combo box or type the info. manually.

The code is not my own. I am quite new to Access and VBA and had to use
this newsgroup as my resource just as I am now.

Private Sub Project_Name_Change()
Dim varProjectID As Variant
varProjectID = DLookup("ProjectID", "Projects", "ProjectName
=[Project_Name] ")
If (Not IsNull(varProjectID)) Then Me![Project Number] = varProjectID
End Sub

Jeffrey K. Ries
 
Can you try moving this code into the AfterUpdate event of the
Project_Name instead and get rid of it from both Change and Exit events?
I also think that you should make the AfterUpdate event of the ID
selector similar, although you won't need the IF condition because the
IDs definitely exist if they are in the lookup list.
I would also try to avoid DLookup:

Private Sub Project_Name_AfterUpdate()
With Me.Form.RecordsetClone
.FindFirst "ProjectName='" & Me.Project_Name & "'"
IF .NoMatch THEN
MsgBox "No such record"
ELSE
Me![Project Number] = .Fields("ProjectID")
END IF
end with

Good luck,
Pavel

Jeffrey K. Ries said:
Certainly.

The OnChange and OnExit code is the same. I find that I need both in order
to either use the combo box or type the info. manually.

The code is not my own. I am quite new to Access and VBA and had to use
this newsgroup as my resource just as I am now.

Private Sub Project_Name_Change()
Dim varProjectID As Variant
varProjectID = DLookup("ProjectID", "Projects", "ProjectName
=[Project_Name] ")
If (Not IsNull(varProjectID)) Then Me![Project Number] = varProjectID
End Sub

Jeffrey K. Ries

Pavel Romashkin said:
Can you post the onExit event code?

Pavel
Click to expand...
Click to expand...
 
Thank you for your help.

I tried your code directly and I got an error. I am fairly new to VBA so I
could not immediately debug it.

However, I did try to simply move my original code into the AfterUpdate
event and it is now working fine.

I will try a bit more to debug the code you sent me to get it working
perfect.

Thanks again,
Jeffrey K. Ries


Pavel Romashkin said:
Can you try moving this code into the AfterUpdate event of the
Project_Name instead and get rid of it from both Change and Exit events?
I also think that you should make the AfterUpdate event of the ID
selector similar, although you won't need the IF condition because the
IDs definitely exist if they are in the lookup list.
I would also try to avoid DLookup:

Private Sub Project_Name_AfterUpdate()
With Me.Form.RecordsetClone
.FindFirst "ProjectName='" & Me.Project_Name & "'"
IF .NoMatch THEN
MsgBox "No such record"
ELSE
Me![Project Number] = .Fields("ProjectID")
END IF
end with

Good luck,
Pavel

Jeffrey K. Ries said:
Certainly.

The OnChange and OnExit code is the same. I find that I need both in order
to either use the combo box or type the info. manually.

The code is not my own. I am quite new to Access and VBA and had to use
this newsgroup as my resource just as I am now.

Private Sub Project_Name_Change()
Dim varProjectID As Variant
varProjectID = DLookup("ProjectID", "Projects", "ProjectName
=[Project_Name] ")
If (Not IsNull(varProjectID)) Then Me![Project Number] = varProjectID
End Sub

Jeffrey K. Ries

Pavel Romashkin said:
Can you post the onExit event code?

Pavel

:

I have a subform which I am trying to perfect, but I am running into a
problem. I have two fields which push values upon each other. They are
'Project Name' and 'Project Number', the theory is that a user can choose
one or the other and the opposite will be automatically filled in. This
works fine through combo boxes with event procedures attached to the
OnChange and OnExit properties.

The problem is that if the focus is on one of these combo boxes then I
cannot select another record in the list. I first have to select another
coulmn in that record and then on another record.

Has this happened to anyone else? Is there a way around it?

Thanks,
Jeffrey K. Ries
Click to expand...
Click to expand...
Click to expand...
 
Sorry! It was air code. I am stuck here on a Mac with no Access and
can't write anything that makes sense :-(
I am glad you got it to work.
Good luck,
Pavel

Jeffrey K. Ries said:
Thank you for your help.

I tried your code directly and I got an error. I am fairly new to VBA so I
could not immediately debug it.

However, I did try to simply move my original code into the AfterUpdate
event and it is now working fine.

I will try a bit more to debug the code you sent me to get it working
perfect.

Thanks again,
Jeffrey K. Ries

Pavel Romashkin said:
Can you try moving this code into the AfterUpdate event of the
Project_Name instead and get rid of it from both Change and Exit events?
I also think that you should make the AfterUpdate event of the ID
selector similar, although you won't need the IF condition because the
IDs definitely exist if they are in the lookup list.
I would also try to avoid DLookup:

Private Sub Project_Name_AfterUpdate()
With Me.Form.RecordsetClone
.FindFirst "ProjectName='" & Me.Project_Name & "'"
IF .NoMatch THEN
MsgBox "No such record"
ELSE
Me![Project Number] = .Fields("ProjectID")
END IF
end with

Good luck,
Pavel

Jeffrey K. Ries said:
Certainly.

The OnChange and OnExit code is the same. I find that I need both in order
to either use the combo box or type the info. manually.

The code is not my own. I am quite new to Access and VBA and had to use
this newsgroup as my resource just as I am now.

Private Sub Project_Name_Change()
Dim varProjectID As Variant
varProjectID = DLookup("ProjectID", "Projects", "ProjectName
=[Project_Name] ")
If (Not IsNull(varProjectID)) Then Me![Project Number] = varProjectID
End Sub

Jeffrey K. Ries

Can you post the onExit event code?

Pavel

:

I have a subform which I am trying to perfect, but I am running into a
problem. I have two fields which push values upon each other. They are
'Project Name' and 'Project Number', the theory is that a user can
choose
one or the other and the opposite will be automatically filled in. This
works fine through combo boxes with event procedures attached to the
OnChange and OnExit properties.

The problem is that if the focus is on one of these combo boxes then I
cannot select another record in the list. I first have to select
another
coulmn in that record and then on another record.

Has this happened to anyone else? Is there a way around it?

Thanks,
Jeffrey K. Ries
Click to expand...
Click to expand...
Click to expand...
 
Back
Top