Beginner needs Combo Box help

[serviceman]

Hi all!
I'm a beginning Access 2003 user, and I'm having some trouble with combo
boxes. Here's my trouble:
I have a form where I want to edit or delete students from the table STUDENTS.
This table has an IDENTITY column (named IDENTITY), and it also has a
calculated column that concatenates FST_NAM(first name) and LST_NAM(last name)
; this column is named FULL_NAM(full name). This form also has a 'Delete
record' button. I have created a combobox(unbound) to look up records on the
form using FULL_NAM (Combo104).The problem is the way the combo box displays;
When I open the form the combo box text area is blank (this is ok), and the
list is populated correctly. When I select a record it displays fine, but if
I delete that record it stays in the text area of the combo box and in the
list as well. Through much trial and error I have gotten the list to update
by adding DoCmd.Requery "Combo104" to the code, but I cannot get the text
area to go to the next record, nor can I get the form to just clear
completely when I delete. Clearing the form and resetting the list would be
the preferred setup, but I'm L-O-S-T! I would also like to put a combo box on
the IDENTITY column that would function in the same way. ANY help would be
greatly appreciated!!! I would really like to get a better handle on Access,
as there are several daily tasks that I can make easier if I can just figure
it out!
Andy

 

[Klatuu]

Sound like you are pretty much on the right track. What you are experiencing
with the combo after the requery is normal. It is basically starting over,
so it will go to showing nothing in the combo box. As to the form, the
deleted record is deleted in the table, but is still in the recordset for the
form, so what you need to do is requery the form. This will also requery the
combo, so you could just use Me.Requery rather than the requery on the combo.
Now, what you will not like is that, as I stated previously, it is like
starting over, so the form will display the first record in the recordset
after the requery.

It is possible to make it go to the next record in the recordset (or at
least appear to the user to do so), but it takes some trickery and coding.
Below is a sub I use to do exactly that. I call it from the Click event of
my delete command button. I put it in a standard module so I can call it
from multiple forms. I call it like this:

Call DelCurrentRec(Me)


Public Sub DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Sub

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Sub

 

[serviceman]

Hey Klatuu!
Thanks for the quick response; Now all I need to do is get it to work!
In my bumbling coding attempts, I have put the DoCmd.Requery line in like
this:
(This is all the code for the form)
------------------------------------------------------------------------------
-------------
Option Compare Database

Private Sub Delete_Record_Click()
On Error GoTo Err_Delete_Record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record_Click:

Exit Sub

Err_Delete_Record_Click:
MsgBox Err.Description
Resume Exit_Delete_Record_Click

End Sub



Private Sub Save_Changes__Click()
On Error GoTo Err_Save_Changes__Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Changes__Click:
Exit Sub

Err_Save_Changes__Click:
MsgBox Err.Description
Resume Exit_Save_Changes__Click

End Sub


Private Sub Command99_Click()
On Error GoTo Err_Command99_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command99_Click:
Exit Sub

Err_Command99_Click:
MsgBox Err.Description
Resume Exit_Command99_Click

End Sub
Private Sub Combo108_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[IDENTITY] = " & Str(Nz(Me![Combo108], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Requery
"Combo108"<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<Here it is!
End Sub
Now, if I replace it with Me.Requery it doesn't work. Am I correct in
assuming that this Me.requery needs to go somewheres in the 'Delete Button'
code rather than the ComboBox code?
Andy

Sound like you are pretty much on the right track. What you are experiencing
with the combo after the requery is normal. It is basically starting over,
so it will go to showing nothing in the combo box. As to the form, the
deleted record is deleted in the table, but is still in the recordset for the
form, so what you need to do is requery the form. This will also requery the
combo, so you could just use Me.Requery rather than the requery on the combo.
Now, what you will not like is that, as I stated previously, it is like
starting over, so the form will display the first record in the recordset
after the requery.

It is possible to make it go to the next record in the recordset (or at
least appear to the user to do so), but it takes some trickery and coding.
Below is a sub I use to do exactly that. I call it from the Click event of
my delete command button. I put it in a standard module so I can call it
from multiple forms. I call it like this:

Call DelCurrentRec(Me)

Public Sub DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Sub

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Sub

Hi all!
I'm a beginning Access 2003 user, and I'm having some trouble with combo

[quoted text clipped - 18 lines]

it out!
Andy

 

[Klatuu]

It should be in the code of the click event of the delete command button.
Get away from using those menu bar command. They are impossible to read.
Use real VBA.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

Also, I don't believe it will work with the code I sent. In this case, you
are actually deleting from the recordsetclone. I think you would end up
deleting either the record before it or after it depending on the position of
the record, because the code above is deleting the current record in the form.

Hey Klatuu!
Thanks for the quick response; Now all I need to do is get it to work!
In my bumbling coding attempts, I have put the DoCmd.Requery line in like
this:
(This is all the code for the form)
------------------------------------------------------------------------------
-------------
Option Compare Database

Private Sub Delete_Record_Click()
On Error GoTo Err_Delete_Record_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

Exit_Delete_Record_Click:

Exit Sub

Err_Delete_Record_Click:
MsgBox Err.Description
Resume Exit_Delete_Record_Click

End Sub



Private Sub Save_Changes__Click()
On Error GoTo Err_Save_Changes__Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_Save_Changes__Click:
Exit Sub

Err_Save_Changes__Click:
MsgBox Err.Description
Resume Exit_Save_Changes__Click

End Sub


Private Sub Command99_Click()
On Error GoTo Err_Command99_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command99_Click:
Exit Sub

Err_Command99_Click:
MsgBox Err.Description
Resume Exit_Command99_Click

End Sub
Private Sub Combo108_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.Find "[IDENTITY] = " & Str(Nz(Me![Combo108], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
DoCmd.Requery
"Combo108"<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<Here it is!
End Sub
Now, if I replace it with Me.Requery it doesn't work. Am I correct in
assuming that this Me.requery needs to go somewheres in the 'Delete Button'
code rather than the ComboBox code?
Andy

Sound like you are pretty much on the right track. What you are experiencing
with the combo after the requery is normal. It is basically starting over,
so it will go to showing nothing in the combo box. As to the form, the
deleted record is deleted in the table, but is still in the recordset for the
form, so what you need to do is requery the form. This will also requery the
combo, so you could just use Me.Requery rather than the requery on the combo.
Now, what you will not like is that, as I stated previously, it is like
starting over, so the form will display the first record in the recordset
after the requery.

It is possible to make it go to the next record in the recordset (or at
least appear to the user to do so), but it takes some trickery and coding.
Below is a sub I use to do exactly that. I call it from the Click event of
my delete command button. I put it in a standard module so I can call it
from multiple forms. I call it like this:

Call DelCurrentRec(Me)

Public Sub DelCurrentRec(ByRef frmSomeForm As Form)
Dim rst As Recordset

On Error GoTo DelCurrentRec_Error

Application.Echo False
With frmSomeForm
Set rst = .RecordsetClone
rst.Bookmark = .Bookmark
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MoveNext
Else
.Recordset.MovePrevious
End If
rst.Delete
If .Recordset.AbsolutePosition > 0 Then
.Recordset.MovePrevious
Else
.Recordset.MoveNext
End If
End With

DelCurrentRec_Exit:

On Error Resume Next
rst.Close
Set rst = Nothing
Application.Echo True
Exit Sub

DelCurrentRec_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure DelCurrentRec of Module modFormOperations"
GoTo DelCurrentRec_Exit

End Sub

Hi all!
I'm a beginning Access 2003 user, and I'm having some trouble with combo

[quoted text clipped - 18 lines]

it out!
Andy

 

[serviceman via AccessMonster.com]

Klatuu;
This site is A-W-E-S-O-M-E!! The best advice so far has been
"Use real VBA"...
Ok, so I went out and picked up a couple books on VBA, and have gotten
several things straightened out, including this combo box issue. Now, being
armed with new knowledge and feeling brave, I decided to try and 'hop up'
this combo box even more by setting it up so that when a user types in data
that is not in the list, it opens a form to add the record and then returns
to the original form. I am having an issue with DLookup in the following code:

--------------------------------------------------------------------------
Private Sub Combo108_NotInList(NewData As String, Response As Integer)
If MsgBox("This student is not on file." & _
"Would you like to add a new student?", vbYesNo) = vbYes Then
'Open the student entry form
DoCmd.OpenForm "STUDENTENTRY", acNormal, , , acFormAdd, acDialog, NewData
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ & NewData & """"))
Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If

End Sub

It pops up the new form ok, but after I enter the data and return to the 1st
form I get an error that says 'Invalid column name Sally Ride' where 'Sally
Ride' is the Student I just entered. This is the value of NewData, but why is
it seeing this data as a column name? I assume I am missing quote marks or
something, but I just can't get it...

It should be in the code of the click event of the delete command button.
Get away from using those menu bar command. They are impossible to read.
Use real VBA.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

Also, I don't believe it will work with the code I sent. In this case, you
are actually deleting from the recordsetclone. I think you would end up
deleting either the record before it or after it depending on the position of
the record, because the code above is deleting the current record in the form.

Hey Klatuu!
Thanks for the quick response; Now all I need to do is get it to work!

[quoted text clipped - 125 lines]

 

[Klatuu]

Good for You! Hoorah!
Your syntax is just a little off. Getting the right number of quotes, to
me, is the hardest thing to do correctly. I am ashamed to say I still have
to play with it until I get it right. What I do is go to the immediate
window and work on it. For example, in this case I typed in
Newdata = "Sally Ride"
Then tried several different combinations until I found one that worked. It
is this:

"FULL_NAM = """ & NewData & """

So the full command is:
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ & NewData & """))

One other thing that will help you is using indentation in your coding. It
makes it so much easier to read. Here is how I would structure your code:

Private Sub Combo108_NotInList(NewData As String, Response As Integer)

If MsgBox("This student is not on file." & _
"Would you like to add a new student?", vbYesNo) = vbYes Then
'Open the student entry form
DoCmd.OpenForm "STUDENTENTRY", acNormal, , , acFormAdd, _
acDialog, NewData
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ _
& NewData """)) Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If

End Sub

Klatuu;
This site is A-W-E-S-O-M-E!! The best advice so far has been
"Use real VBA"...
Ok, so I went out and picked up a couple books on VBA, and have gotten
several things straightened out, including this combo box issue. Now, being
armed with new knowledge and feeling brave, I decided to try and 'hop up'
this combo box even more by setting it up so that when a user types in data
that is not in the list, it opens a form to add the record and then returns
to the original form. I am having an issue with DLookup in the following code:

--------------------------------------------------------------------------
Private Sub Combo108_NotInList(NewData As String, Response As Integer)
If MsgBox("This student is not on file." & _
"Would you like to add a new student?", vbYesNo) = vbYes Then
'Open the student entry form
DoCmd.OpenForm "STUDENTENTRY", acNormal, , , acFormAdd, acDialog, NewData
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ & NewData & """"))
Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If

End Sub

It pops up the new form ok, but after I enter the data and return to the 1st
form I get an error that says 'Invalid column name Sally Ride' where 'Sally
Ride' is the Student I just entered. This is the value of NewData, but why is
it seeing this data as a column name? I assume I am missing quote marks or
something, but I just can't get it...

It should be in the code of the click event of the delete command button.
Get away from using those menu bar command. They are impossible to read.
Use real VBA.

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70

Also, I don't believe it will work with the code I sent. In this case, you
are actually deleting from the recordsetclone. I think you would end up
deleting either the record before it or after it depending on the position of
the record, because the code above is deleting the current record in the form.

Hey Klatuu!
Thanks for the quick response; Now all I need to do is get it to work!

[quoted text clipped - 125 lines]

it out!
Andy

 

[serviceman via AccessMonster.com]

Klatuu!
Almost, but not quite! I copied and pasted your new code, and I get two
errors:
1st, the _ in the dlookup line causes an 'invalid character' error.
2nd, with one less quote mark I get a syntax error.
BUT!!! I just thought of something; could this be because I left out the very
important point that this machine is running SQL Server, and that is where
the DB is? Does this have anything to do with how this gets
commented/quoted/bracketed?
I'm getting closer, I can feel the love!
Andy

Good for You! Hoorah!
Your syntax is just a little off. Getting the right number of quotes, to
me, is the hardest thing to do correctly. I am ashamed to say I still have
to play with it until I get it right. What I do is go to the immediate
window and work on it. For example, in this case I typed in
Newdata = "Sally Ride"
Then tried several different combinations until I found one that worked. It
is this:

"FULL_NAM = """ & NewData & """

So the full command is:
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ & NewData & """))

One other thing that will help you is using indentation in your coding. It
makes it so much easier to read. Here is how I would structure your code:

Private Sub Combo108_NotInList(NewData As String, Response As Integer)

If MsgBox("This student is not on file." & _
"Would you like to add a new student?", vbYesNo) = vbYes Then
'Open the student entry form
DoCmd.OpenForm "STUDENTENTRY", acNormal, , , acFormAdd, _
acDialog, NewData
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ _
& NewData """)) Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If

End Sub

Klatuu;
This site is A-W-E-S-O-M-E!! The best advice so far has been

[quoted text clipped - 45 lines]

 

[Klatuu]

The problem you had with the _ is that it is a continuation marker, that is
it is always at the end of the line of code and tells the compile "This
statement is continued on the next line." I put it in there so I could show
formatting technique.

That should solve the problem with the query. If not, I am not familiar
enough with using SQLServer to know if a syntax difference exsits for this
situation. Being it is a DLookup, it should not make a difference. Try
this. As I said earlier, go to the immediate window. type in : Newdata =
"Sally Ride" Then type in:
X = "FULL_NAM = """ & NewData & """
Then type in ?X
What you should see returned is
FULL_NAM = "Sally Ride"
That means the syntax(number of quotes) is correct. If you get something
else, try changing the quotes.

Klatuu!
Almost, but not quite! I copied and pasted your new code, and I get two
errors:
1st, the _ in the dlookup line causes an 'invalid character' error.
2nd, with one less quote mark I get a syntax error.
BUT!!! I just thought of something; could this be because I left out the very
important point that this machine is running SQL Server, and that is where
the DB is? Does this have anything to do with how this gets
commented/quoted/bracketed?
I'm getting closer, I can feel the love!
Andy

Good for You! Hoorah!
Your syntax is just a little off. Getting the right number of quotes, to
me, is the hardest thing to do correctly. I am ashamed to say I still have
to play with it until I get it right. What I do is go to the immediate
window and work on it. For example, in this case I typed in
Newdata = "Sally Ride"
Then tried several different combinations until I found one that worked. It
is this:

"FULL_NAM = """ & NewData & """

So the full command is:
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ & NewData & """))

One other thing that will help you is using indentation in your coding. It
makes it so much easier to read. Here is how I would structure your code:

Private Sub Combo108_NotInList(NewData As String, Response As Integer)

If MsgBox("This student is not on file." & _
"Would you like to add a new student?", vbYesNo) = vbYes Then
'Open the student entry form
DoCmd.OpenForm "STUDENTENTRY", acNormal, , , acFormAdd, _
acDialog, NewData
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "FULL_NAM = """ _
& NewData """)) Then
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If
Else
Response = acDataErrContinue
End If

End Sub

Klatuu;
This site is A-W-E-S-O-M-E!! The best advice so far has been

[quoted text clipped - 45 lines]

it out!
Andy

 

[serviceman via AccessMonster.com]

In between my post and your reply I figured out the continuation marker
thing; one problem out of the way. Following your helpful insight, I
determined that the correct quotes are """ & NewData & """".
That is the second thing out of the way, but it still returns the same
'Invalid column Sally Ride' error.
NOW,
I got to thinking about this; and I think it may be my underlying strucutre
that is screwing things up. This combo box data uses this query:
SELECT IDENTITY, FST_NAM+' '+LST_NAM as FULLNAME
from STUDENTS
The combo box format hides the IDENTITY column so users only see "Sally Ride"
When invalid data is entered, We open the form STUDENTENTRY to enter the new
student.
the column IDENTITY is (autonumber), FST_NAM is 'Sally', LST_NAM is 'Ride',
CALCULATED COLUMN FULL_NAM is concatenation of both.
When I close the form STUDENTENTRY, I get the 'Invalid column name' error.
Can it be that the DLookup query is ok but it is passing it back to the
combobox as the second column, with FULL_NAM as the first column? Does this
make sense? It seems to me that the query would need to return 2 columns to
the STUDENTMAINT form, not 1....
But hey, what do I know??

Andy

The problem you had with the _ is that it is a continuation marker, that is
it is always at the end of the line of code and tells the compile "This
statement is continued on the next line." I put it in there so I could show
formatting technique.

That should solve the problem with the query. If not, I am not familiar
enough with using SQLServer to know if a syntax difference exsits for this
situation. Being it is a DLookup, it should not make a difference. Try
this. As I said earlier, go to the immediate window. type in : Newdata =
"Sally Ride" Then type in:
X = "FULL_NAM = """ & NewData & """
Then type in ?X
What you should see returned is
FULL_NAM = "Sally Ride"
That means the syntax(number of quotes) is correct. If you get something
else, try changing the quotes.

Klatuu!
Almost, but not quite! I copied and pasted your new code, and I get two

[quoted text clipped - 49 lines]

 

[Klatuu]

First, the + sign is only for math. When concatenating strings use &, so
change:
SELECT IDENTITY, FST_NAM+' '+LST_NAM as FULLNAME from STUDENTS
To
SELECT IDENTITY, FST_NAM & ' ' & LST_NAM as FULLNAME from STUDENTS;

Then I don't know what your bound column is in your combo, but for this
case, it should be 1, which would be the IDENTITY column. Now, assuming that
is a unique primary key, this would be better:

If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.MyComboName
& "))

The above assumes IDENTITY is a numeric field.

If you refer to Me.MyComboBox it returns the bound column of the currently
selected row. If you want to get data from other columns, the index will
start with 0 and you can get it like this:
Me.MyComboBox.Column(0) - In your case will return the IDENTITY
Me.MyComboBox.Column(1) - In your case will return the FULLNAME
Not those literals, but their values, for example if Sally Ride was the
current row, then Me.MyComboBox.Column(1) would return Sally Ride.

In between my post and your reply I figured out the continuation marker
thing; one problem out of the way. Following your helpful insight, I
determined that the correct quotes are """ & NewData & """".
That is the second thing out of the way, but it still returns the same
'Invalid column Sally Ride' error.
NOW,
I got to thinking about this; and I think it may be my underlying strucutre
that is screwing things up. This combo box data uses this query:
SELECT IDENTITY, FST_NAM+' '+LST_NAM as FULLNAME
from STUDENTS
The combo box format hides the IDENTITY column so users only see "Sally Ride"
When invalid data is entered, We open the form STUDENTENTRY to enter the new
student.
the column IDENTITY is (autonumber), FST_NAM is 'Sally', LST_NAM is 'Ride',
CALCULATED COLUMN FULL_NAM is concatenation of both.
When I close the form STUDENTENTRY, I get the 'Invalid column name' error.
Can it be that the DLookup query is ok but it is passing it back to the
combobox as the second column, with FULL_NAM as the first column? Does this
make sense? It seems to me that the query would need to return 2 columns to
the STUDENTMAINT form, not 1....
But hey, what do I know??

Andy

The problem you had with the _ is that it is a continuation marker, that is
it is always at the end of the line of code and tells the compile "This
statement is continued on the next line." I put it in there so I could show
formatting technique.

That should solve the problem with the query. If not, I am not familiar
enough with using SQLServer to know if a syntax difference exsits for this
situation. Being it is a DLookup, it should not make a difference. Try
this. As I said earlier, go to the immediate window. type in : Newdata =
"Sally Ride" Then type in:
X = "FULL_NAM = """ & NewData & """
Then type in ?X
What you should see returned is
FULL_NAM = "Sally Ride"
That means the syntax(number of quotes) is correct. If you get something
else, try changing the quotes.

Klatuu!
Almost, but not quite! I copied and pasted your new code, and I get two

[quoted text clipped - 49 lines]

it out!
Andy

 

[serviceman via AccessMonster.com]

'Round and 'round we go!
I am so loving this!
The '+' sign thing is an SQL Server issue; '&' is used in MSAccess DBs, where
'+' is used in SQL Db's. (I think I am saying this right; even if I go to
table creation in Access it wants '+' signs...
Anyway---
I believe your new method is the way to go; I tried it, and now (Surprise!)
there is a new error:
Runtime 170
Line 1 Incorrect syntax near ')'
If I don't debug and just 'end' I then get the generic 'Text not found'
message for the combobox as it is not passing a requery back for the box. I
went to the immediate window (I'm learning!) and I see that Me.Combo108=118
and IDENTITY=118, so the Dlookup should work if we/you can fix the syntax
error...
Closer, Closer......
Andy

First, the + sign is only for math. When concatenating strings use &, so
change:
SELECT IDENTITY, FST_NAM+' '+LST_NAM as FULLNAME from STUDENTS
To
SELECT IDENTITY, FST_NAM & ' ' & LST_NAM as FULLNAME from STUDENTS;

Then I don't know what your bound column is in your combo, but for this
case, it should be 1, which would be the IDENTITY column. Now, assuming that
is a unique primary key, this would be better:

If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.MyComboName
& "))

The above assumes IDENTITY is a numeric field.

If you refer to Me.MyComboBox it returns the bound column of the currently
selected row. If you want to get data from other columns, the index will
start with 0 and you can get it like this:
Me.MyComboBox.Column(0) - In your case will return the IDENTITY
Me.MyComboBox.Column(1) - In your case will return the FULLNAME
Not those literals, but their values, for example if Sally Ride was the
current row, then Me.MyComboBox.Column(1) would return Sally Ride.

In between my post and your reply I figured out the continuation marker
thing; one problem out of the way. Following your helpful insight, I

[quoted text clipped - 42 lines]

 

[serviceman via AccessMonster.com]

CLOSER AND CLOSER!!!
This almost works:
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.Combo108))
Then

This passes the name back to the combo box in STUDENTMAINT without an error!!
Now I just need to get the Form to refresh with the new students info and
this will be good to go!
Since we're having so much fun, any idea how to split NewData into separate
FST_NAM and LST_NAM values so I can pass it into the STUDENTENTRY form? What
the heck, I'll just build a monster!

Andy

'Round and 'round we go!
I am so loving this!
The '+' sign thing is an SQL Server issue; '&' is used in MSAccess DBs, where
'+' is used in SQL Db's. (I think I am saying this right; even if I go to
table creation in Access it wants '+' signs...
Anyway---
I believe your new method is the way to go; I tried it, and now (Surprise!)
there is a new error:
Runtime 170
Line 1 Incorrect syntax near ')'
If I don't debug and just 'end' I then get the generic 'Text not found'
message for the combobox as it is not passing a requery back for the box. I
went to the immediate window (I'm learning!) and I see that Me.Combo108=118
and IDENTITY=118, so the Dlookup should work if we/you can fix the syntax
error...
Closer, Closer......
Andy

First, the + sign is only for math. When concatenating strings use &, so
change:

[quoted text clipped - 24 lines]

 

[serviceman via AccessMonster.com]

Spoke Too soon...
It Doesn't work; the incorrect syntax is still there...

CLOSER AND CLOSER!!!
This almost works:
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.Combo108))
Then

This passes the name back to the combo box in STUDENTMAINT without an error!!
Now I just need to get the Form to refresh with the new students info and
this will be good to go!
Since we're having so much fun, any idea how to split NewData into separate
FST_NAM and LST_NAM values so I can pass it into the STUDENTENTRY form? What
the heck, I'll just build a monster!

Andy

'Round and 'round we go!
I am so loving this!

[quoted text clipped - 19 lines]

 

[Klatuu]

I told you I was week with SQLServer SQL syntax, so maybe the + is the
correct way to do it.

If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.MyComboName
))

Ooops!, perhaps I should read what I wrote before I post, I had the syntax
wrong for the combo box in the previous post. I think the above will work.
Please let me know when you get this working.

'Round and 'round we go!
I am so loving this!
The '+' sign thing is an SQL Server issue; '&' is used in MSAccess DBs, where
'+' is used in SQL Db's. (I think I am saying this right; even if I go to
table creation in Access it wants '+' signs...
Anyway---
I believe your new method is the way to go; I tried it, and now (Surprise!)
there is a new error:
Runtime 170
Line 1 Incorrect syntax near ')'
If I don't debug and just 'end' I then get the generic 'Text not found'
message for the combobox as it is not passing a requery back for the box. I
went to the immediate window (I'm learning!) and I see that Me.Combo108=118
and IDENTITY=118, so the Dlookup should work if we/you can fix the syntax
error...
Closer, Closer......
Andy

First, the + sign is only for math. When concatenating strings use &, so
change:
SELECT IDENTITY, FST_NAM+' '+LST_NAM as FULLNAME from STUDENTS
To
SELECT IDENTITY, FST_NAM & ' ' & LST_NAM as FULLNAME from STUDENTS;

Then I don't know what your bound column is in your combo, but for this
case, it should be 1, which would be the IDENTITY column. Now, assuming that
is a unique primary key, this would be better:

If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.MyComboName
& "))

The above assumes IDENTITY is a numeric field.

If you refer to Me.MyComboBox it returns the bound column of the currently
selected row. If you want to get data from other columns, the index will
start with 0 and you can get it like this:
Me.MyComboBox.Column(0) - In your case will return the IDENTITY
Me.MyComboBox.Column(1) - In your case will return the FULLNAME
Not those literals, but their values, for example if Sally Ride was the
current row, then Me.MyComboBox.Column(1) would return Sally Ride.

In between my post and your reply I figured out the continuation marker
thing; one problem out of the way. Following your helpful insight, I

[quoted text clipped - 42 lines]

it out!
Andy

 

[Klatuu]

Are we working in two different forms here? I guess I missed that before.
The syntax should be correct. I can't see a problem with it. Is the code in
the same form as the combo box? Post back with the complete procedure and
let me look at it. As to the NewData, I think it will have the Identity in
it rather than the full_name, but I never use it, so I am not really
positive. What I do know, is that column(1) of your combo box will have it.
And, when you ask if you could split it, you gave the answer. Use the Split
function.

Dim varBothNames as Variant

varBothNames = Split(Me.MyCombo.Column(1), " ")

Based on full_name being first & " " & last, there will always be a space
between them. So,
varBothNames(0) will = Sally
varBothNames(1) will = Ride

Now for the gottcha. If you have either or both of the names that have
spaces in them, you will get a different result. The Split function
separates what you want to split (1st argument) on the character specified in
the second argument.

Spoke Too soon...
It Doesn't work; the incorrect syntax is still there...

CLOSER AND CLOSER!!!
This almost works:
If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.Combo108))
Then

This passes the name back to the combo box in STUDENTMAINT without an error!!
Now I just need to get the Form to refresh with the new students info and
this will be good to go!
Since we're having so much fun, any idea how to split NewData into separate
FST_NAM and LST_NAM values so I can pass it into the STUDENTENTRY form? What
the heck, I'll just build a monster!

Andy

'Round and 'round we go!
I am so loving this!

[quoted text clipped - 19 lines]

it out!
Andy

 

[serviceman via AccessMonster.com]

Hey Klatuu!!
One step closer... Part of this issue IS SQL server:

If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = '" & Me.Combo108 &
"'")) Then

Notice the SINGLE quotes around Me.Combo108; This was the syntax error!
SO,
Now when I add the new student and close STUDENTENTRY, Sally Ride shows up in
the Combobox with no error. Only problem is, The Rest of the form does not
fill with her data, and if I select her name in the combo box it does nothing.
If it helps, The IDENTITY box on STUDENTMAINT displays (Autonumber) rather
than the new ID number. Of course, if I close the form and reopen it it works
fine....
Andy

I told you I was week with SQLServer SQL syntax, so maybe the + is the
correct way to do it.

If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.MyComboName
))

Ooops!, perhaps I should read what I wrote before I post, I had the syntax
wrong for the combo box in the previous post. I think the above will work.
Please let me know when you get this working.

'Round and 'round we go!
I am so loving this!

[quoted text clipped - 42 lines]

 

[Klatuu]

Great. Your form will not automatically populate. You have to move to the
record for the vaule you have selected. Here is one I copies from one of my
apps where the user selects an Activity number. The code in the After Update
event of the combo moves to that record:

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtMactivity = Me.cboActivity
Me.frmSubAttributeTable!txtMactivity = Me.cboActivity
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
Me.cboActivity = Null
Call SetNavButtons(Me)
Me.txtDescription.SetFocus
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

End Sub

Hey Klatuu!!
One step closer... Part of this issue IS SQL server:

If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = '" & Me.Combo108 &
"'")) Then

Notice the SINGLE quotes around Me.Combo108; This was the syntax error!
SO,
Now when I add the new student and close STUDENTENTRY, Sally Ride shows up in
the Combobox with no error. Only problem is, The Rest of the form does not
fill with her data, and if I select her name in the combo box it does nothing.
If it helps, The IDENTITY box on STUDENTMAINT displays (Autonumber) rather
than the new ID number. Of course, if I close the form and reopen it it works
fine....
Andy

I told you I was week with SQLServer SQL syntax, so maybe the + is the
correct way to do it.

If IsNull(DLookup("[IDENTITY]", "STUDENTS", "[IDENTITY] = " & Me.MyComboName
))

Ooops!, perhaps I should read what I wrote before I post, I had the syntax
wrong for the combo box in the previous post. I think the above will work.
Please let me know when you get this working.

'Round and 'round we go!
I am so loving this!

[quoted text clipped - 42 lines]

it out!
Andy

 

[serviceman via AccessMonster.com]

Thats it! It works! Single quotes and your last code snippet did the trick!
Thanks so much for the advice and support; it goes a LONG way. My background
is all SQL (DBA) and I figured Access would be fairly easy to move to.What an
adventure this has been! Now I'm going to build a form the has several list
boxes in it that will autocreate a table after the user enters their choices.
This should be a hoot...
Andy

Great. Your form will not automatically populate. You have to move to the
record for the vaule you have selected. Here is one I copies from one of my
apps where the user selects an Activity number. The code in the After Update
event of the combo moves to that record:

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtMactivity = Me.cboActivity
Me.frmSubAttributeTable!txtMactivity = Me.cboActivity
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
Me.cboActivity = Null
Call SetNavButtons(Me)
Me.txtDescription.SetFocus
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

End Sub

Hey Klatuu!!
One step closer... Part of this issue IS SQL server:

[quoted text clipped - 27 lines]

 

[Klatuu]

It has been fun. Glad to help someone who is so easy to work with.

One last thing regarding creating a table. If you plan to use a Make Table
query, be aware it guesses at data types, so you may not get exactly what you
want. It also has the bad habit of assigning the default field length to
every text field. As shipped, it is set at 255, so you will waste a lot of
space. You can change that in Tools, Options, Tables/Queries tab, but is
will still waste space or possibly truncate data. They way I handle this
situation is to create a table with the structure I want, then when I need to
put data in it, delete the data that is currently in it, then use and Append
query to load the new data.

Good luck with Access. As fast as you catch on, I am sure you will be a
star in no time.

Thats it! It works! Single quotes and your last code snippet did the trick!
Thanks so much for the advice and support; it goes a LONG way. My background
is all SQL (DBA) and I figured Access would be fairly easy to move to.What an
adventure this has been! Now I'm going to build a form the has several list
boxes in it that will autocreate a table after the user enters their choices.
This should be a hoot...
Andy

Great. Your form will not automatically populate. You have to move to the
record for the vaule you have selected. Here is one I copies from one of my
apps where the user selects an Activity number. The code in the After Update
event of the combo moves to that record:

Private Sub cboActivity_AfterUpdate()
Dim rst As Recordset
On Error GoTo cboActivity_AfterUpdate_Error

If Not IsNull(Me.cboActivity) Then
Set rst = Me.RecordsetClone
rst.FindFirst "[Activity] = '" & Me.cboActivity & "'"
If rst.NoMatch Then
If MsgBox("Add Activity Number " & Me.cboActivity & " To the
Attribute Table", _
vbYesNo + vbQuestion + vbDefaultButton2, "Activity Not
Found") _
= vbYes Then
DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Me.txtActivity = Me.cboActivity
Me.txtMactivity = Me.cboActivity
Me.frmSubAttributeTable!txtMactivity = Me.cboActivity
Else
Me.cboActivity = Null
End If
Else
Me.Bookmark = rst.Bookmark
End If
Set rst = Nothing
Me.cboActivity = Null
Call SetNavButtons(Me)
Me.txtDescription.SetFocus
End If
cboActivity_AfterUpdate_Exit:

On Error Resume Next
rst.Close
Exit Sub

cboActivity_AfterUpdate_Error:

MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure cboActivity_AfterUpdate of VBA Document
Form_frmAttributetable"
GoTo cboActivity_AfterUpdate_Exit

End Sub

Hey Klatuu!!
One step closer... Part of this issue IS SQL server:

[quoted text clipped - 27 lines]

it out!
Andy

 

[serviceman via AccessMonster.com]

Thanks Klatuu,
As I'm reading your last post I have redesigned the table structure for my
scheduling, so that there will be no need to create tables on the fly. I am
going through the pains of building a calendar table (what fun; calendar data
was engineered by some people chained to a rock in the desert!
One thing I haven't quite figured out yet:
Can a field or control be 'grayed out" or disabled based on the current value
in another field or control? I don't want to give too much power to the
masses!
Andy

It has been fun. Glad to help someone who is so easy to work with.

One last thing regarding creating a table. If you plan to use a Make Table
query, be aware it guesses at data types, so you may not get exactly what you
want. It also has the bad habit of assigning the default field length to
every text field. As shipped, it is set at 255, so you will waste a lot of
space. You can change that in Tools, Options, Tables/Queries tab, but is
will still waste space or possibly truncate data. They way I handle this
situation is to create a table with the structure I want, then when I need to
put data in it, delete the data that is currently in it, then use and Append
query to load the new data.

Good luck with Access. As fast as you catch on, I am sure you will be a
star in no time.

Thats it! It works! Single quotes and your last code snippet did the trick!
Thanks so much for the advice and support; it goes a LONG way. My background

[quoted text clipped - 57 lines]