How do I display data select N a combo box in individual txt box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Part of the code I am working. Any help is greatly appreciated

'Declared labels
"Open database to access tables
'Initialized labels

Set recd1 = New ADODB.Recordset
myString = "SELECT* FROM DET1PERSONNEL WHERE FIRST_NAME = '" & FName & "'
AND LAST_NAME = '" & LName & "';"
recd1.Open myString, conn1, adOpenKeyset, adLockOptimistic


If Not recd1.BOF And Not recd1.EOF Then

Me.FIRST_NAME.SetFocus
Me.FIRST_NAME.Text = FName

Me.LAST_NAME.SetFocus
Me.LAST_NAME.Text = "LAST_NAME"

Else
MsgBox "This person is not in this database"
End If


I get this error when I run the code:
The Macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing Microsoft Office Access from saving the data in the
field
 
If you dont mind, I can show you a different approach

If Not IsNull(DLookUp("FIRST_NAME","DET1PERSONNEL","FIRST_NAME = '" & FName
& "' AND LAST_NAME = '" & LName & "'") Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If
 
Just to fix my code, I need another closing brackets
If Not IsNull(DLookUp("FIRST_NAME","DET1PERSONNEL","FIRST_NAME = '" & FName
& "' AND LAST_NAME = '" & LName & "'")) Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
 
I used this code:

If Not IsNull(DLookup("[FIRST_NAME]", "DET1PERSONNEL", "[FIRST_NAME] = '" &
FName & "' AND [LAST_NAME] = '" & LName & "';")) Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If

but I get this error. Syntax error in query expression '[FIRST_NAME] =
'data' AND [LAST_NAME] = 'data';'

I also used this code:

If Not IsNull(DLookup("[FIRST_NAME]", "DET1PERSONNEL", "[FIRST_NAME] = '" &
FName & "' AND [LAST_NAME] = " & LName)) Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If

but I get an error. "You canceled the previous operation


This is what I am attempting to do.

I would like for the data I select from a combo box to display in individual
text box's on the form I have placed the comb box. The combo box and the
text box's are on the same form.

When I select data from the combo box I place selected data into three
individual variables.

I then set a New ADODB.recordset. After that I store the complete string of
data determine by selection from the combo box in a string variable. The
data stored in the string variable is pulled from the table that populates
the query that populates the combo box.

The code to display selected data on the form in individual text box's is
placed inside a if statement.

FName = ""
LName = ""

num1 = InStr(1, Combo26.Text, Chr(32))
FName = Left(Combo26.Text, (num1 - 1))
LName = Mid(Combo26.Text, (num1 + 1))

Set recd1 = New ADODB.Recordset
myString = "SELECT* FROM DET1PERSONNEL WHERE FIRST_NAME = '" & FName & "'
AND LAST_NAME = '" & LName & "';"
recd1.Open myString, conn1, adOpenKeyset, adLockOptimistic

If Not recd1.BOF And Not recd1.EOF Then

Your code from previous post was placed here

Else
MsgBox "This person is not in this database"
End If
 
Ok I figured out what I did wrong. I removed the semicolon between the
quotes. it now does what I intended it to do. Your code was very helpful.
Thanks

AHP said:
I used this code:

If Not IsNull(DLookup("[FIRST_NAME]", "DET1PERSONNEL", "[FIRST_NAME] = '" &
FName & "' AND [LAST_NAME] = '" & LName & "';")) Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If

but I get this error. Syntax error in query expression '[FIRST_NAME] =
'data' AND [LAST_NAME] = 'data';'

I also used this code:

If Not IsNull(DLookup("[FIRST_NAME]", "DET1PERSONNEL", "[FIRST_NAME] = '" &
FName & "' AND [LAST_NAME] = " & LName)) Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If

but I get an error. "You canceled the previous operation


This is what I am attempting to do.

I would like for the data I select from a combo box to display in individual
text box's on the form I have placed the comb box. The combo box and the
text box's are on the same form.

When I select data from the combo box I place selected data into three
individual variables.

I then set a New ADODB.recordset. After that I store the complete string of
data determine by selection from the combo box in a string variable. The
data stored in the string variable is pulled from the table that populates
the query that populates the combo box.

The code to display selected data on the form in individual text box's is
placed inside a if statement.

FName = ""
LName = ""

num1 = InStr(1, Combo26.Text, Chr(32))
FName = Left(Combo26.Text, (num1 - 1))
LName = Mid(Combo26.Text, (num1 + 1))

Set recd1 = New ADODB.Recordset
myString = "SELECT* FROM DET1PERSONNEL WHERE FIRST_NAME = '" & FName & "'
AND LAST_NAME = '" & LName & "';"
recd1.Open myString, conn1, adOpenKeyset, adLockOptimistic

If Not recd1.BOF And Not recd1.EOF Then

Your code from previous post was placed here

Else
MsgBox "This person is not in this database"
End If



Ofer said:
Just to fix my code, I need another closing brackets
If Not IsNull(DLookUp("FIRST_NAME","DET1PERSONNEL","FIRST_NAME = '" & FName
& "' AND LAST_NAME = '" & LName & "'")) Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck
Click to expand...
Click to expand...
 
Your very welcome
--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benefit from it.

Good luck



AHP said:
Ok I figured out what I did wrong. I removed the semicolon between the
quotes. it now does what I intended it to do. Your code was very helpful.
Thanks

AHP said:
I used this code:

If Not IsNull(DLookup("[FIRST_NAME]", "DET1PERSONNEL", "[FIRST_NAME] = '" &
FName & "' AND [LAST_NAME] = '" & LName & "';")) Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If

but I get this error. Syntax error in query expression '[FIRST_NAME] =
'data' AND [LAST_NAME] = 'data';'

I also used this code:

If Not IsNull(DLookup("[FIRST_NAME]", "DET1PERSONNEL", "[FIRST_NAME] = '" &
FName & "' AND [LAST_NAME] = " & LName)) Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If

but I get an error. "You canceled the previous operation


This is what I am attempting to do.

I would like for the data I select from a combo box to display in individual
text box's on the form I have placed the comb box. The combo box and the
text box's are on the same form.

When I select data from the combo box I place selected data into three
individual variables.

I then set a New ADODB.recordset. After that I store the complete string of
data determine by selection from the combo box in a string variable. The
data stored in the string variable is pulled from the table that populates
the query that populates the combo box.

The code to display selected data on the form in individual text box's is
placed inside a if statement.

FName = ""
LName = ""

num1 = InStr(1, Combo26.Text, Chr(32))
FName = Left(Combo26.Text, (num1 - 1))
LName = Mid(Combo26.Text, (num1 + 1))

Set recd1 = New ADODB.Recordset
myString = "SELECT* FROM DET1PERSONNEL WHERE FIRST_NAME = '" & FName & "'
AND LAST_NAME = '" & LName & "';"
recd1.Open myString, conn1, adOpenKeyset, adLockOptimistic

If Not recd1.BOF And Not recd1.EOF Then

Your code from previous post was placed here

Else
MsgBox "This person is not in this database"
End If



Ofer said:
Just to fix my code, I need another closing brackets
If Not IsNull(DLookUp("FIRST_NAME","DET1PERSONNEL","FIRST_NAME = '" & FName
& "' AND LAST_NAME = '" & LName & "'")) Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

If you dont mind, I can show you a different approach

If Not IsNull(DLookUp("FIRST_NAME","DET1PERSONNEL","FIRST_NAME = '" & FName
& "' AND LAST_NAME = '" & LName & "'") Then
Me.FIRST_NAME = FName
Me.LAST_NAME = LName
Else
MsgBox "This person is not in this database"
End If

--
If I answered your question, please mark it as an answer. That way, it will
stay saved for a longer time, so other can benifit from it.

Good luck



:

Part of the code I am working. Any help is greatly appreciated

'Declared labels
"Open database to access tables
'Initialized labels

Set recd1 = New ADODB.Recordset
myString = "SELECT* FROM DET1PERSONNEL WHERE FIRST_NAME = '" & FName & "'
AND LAST_NAME = '" & LName & "';"
recd1.Open myString, conn1, adOpenKeyset, adLockOptimistic


If Not recd1.BOF And Not recd1.EOF Then

Me.FIRST_NAME.SetFocus
Me.FIRST_NAME.Text = FName

Me.LAST_NAME.SetFocus
Me.LAST_NAME.Text = "LAST_NAME"

Else
MsgBox "This person is not in this database"
End If


I get this error when I run the code:
The Macro or function set to the BeforeUpdate or ValidationRule property for
this field is preventing Microsoft Office Access from saving the data in the
field
Click to expand...
Click to expand...
Click to expand...
 
Back
Top