Filling in form with recordset HELP

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

Guest

I have 2 databases i am working with, the first is an oracle database from
which i am filling out a form to then put data in an access database.
However, i can not figure out how to get the contents of the query
(recordset) to appear in all the text boxes on my form. So far i am using a
text box with a query button. When i click the query button the results
appear in the table that comes up, but i can not no matter what i try get
them to show up in the text boxes. Any ideas would be of GREAT help.
 
Is your form bound or unbound? Based on your post, I am guessing it is
unbound. If this is correct, then you will have to load the fields into your
form using VBA.
Here is an example of that:

Private Function LoadRecord(strCode As String) As Boolean
Dim rst As Recordset

Set rst = GetTableRecord("qselMtbCounty", strCode)
If rst.RecordCount = 0 Then
LoadRecord = False
Else
LoadRecord = True
Me.txtCode = rst![COUNTYCODE]
Me.txtDisplayDescription = rst![DESCRIPTION]
'FIX - put fields in here for loading controls
Me.chkMetro = rst![METRO]
Me.txtStateVal = rst![STATE_VAL]
Me.chkStateFld = rst![STATE_FLD]
Me.txtStartDate = rst![START_DTE]
Me.txtEndDate = rst![END_DTE]
End If
'Used later for data validation
iMaxCodeLen = rst.Fields("COUNTYCODE").Size
iMaxNameLen = rst.Fields("DESCRIPTION").Size
iMaxValLen = rst.Fields("STATE_VAL").Size

rst.Close
Set rst = Nothing

End Function
 
Thanks for the help.


I have since tried to use the Dlookup function, with little success. My
fields are bound to the access database, is this a bad thing? And if so how
do you post the data without binding them?
Klatuu said:
Is your form bound or unbound? Based on your post, I am guessing it is
unbound. If this is correct, then you will have to load the fields into your
form using VBA.
Here is an example of that:

Private Function LoadRecord(strCode As String) As Boolean
Dim rst As Recordset

Set rst = GetTableRecord("qselMtbCounty", strCode)
If rst.RecordCount = 0 Then
LoadRecord = False
Else
LoadRecord = True
Me.txtCode = rst![COUNTYCODE]
Me.txtDisplayDescription = rst![DESCRIPTION]
'FIX - put fields in here for loading controls
Me.chkMetro = rst![METRO]
Me.txtStateVal = rst![STATE_VAL]
Me.chkStateFld = rst![STATE_FLD]
Me.txtStartDate = rst![START_DTE]
Me.txtEndDate = rst![END_DTE]
End If
'Used later for data validation
iMaxCodeLen = rst.Fields("COUNTYCODE").Size
iMaxNameLen = rst.Fields("DESCRIPTION").Size
iMaxValLen = rst.Fields("STATE_VAL").Size

rst.Close
Set rst = Nothing

End Function

ccxc2003 said:
I have 2 databases i am working with, the first is an oracle database from
which i am filling out a form to then put data in an access database.
However, i can not figure out how to get the contents of the query
(recordset) to appear in all the text boxes on my form. So far i am using a
text box with a query button. When i click the query button the results
appear in the table that comes up, but i can not no matter what i try get
them to show up in the text boxes. Any ideas would be of GREAT help.
Click to expand...
Click to expand...
 
I don't understand what you mean by "My fields are bound to the access
database".
Fields are bound to controls on a form, but not to the database itself. A
bound form means that you have identified a Row Source for your form. You
then bind fields from the Row Source (a table or query) to controls on the
form. This is the most common way to get the field data to display on your
form. Each time you select a new record, the field data is loaded into the
form control it is bound to. I think in your case, you are using an unbound
form, which means the form does not have a row source and you must manipulate
record movement and data display and updating programmatically.
The example I posted is for use with an unbound form. You have more control
over the form's behaviour with unbound forms, but it takes a lot more
programming.
Post your Dlookup code that is not working, and let's see if we can fix that.

ccxc2003 said:
Thanks for the help.


I have since tried to use the Dlookup function, with little success. My
fields are bound to the access database, is this a bad thing? And if so how
do you post the data without binding them?
Klatuu said:
Is your form bound or unbound? Based on your post, I am guessing it is
unbound. If this is correct, then you will have to load the fields into your
form using VBA.
Here is an example of that:

Private Function LoadRecord(strCode As String) As Boolean
Dim rst As Recordset

Set rst = GetTableRecord("qselMtbCounty", strCode)
If rst.RecordCount = 0 Then
LoadRecord = False
Else
LoadRecord = True
Me.txtCode = rst![COUNTYCODE]
Me.txtDisplayDescription = rst![DESCRIPTION]
'FIX - put fields in here for loading controls
Me.chkMetro = rst![METRO]
Me.txtStateVal = rst![STATE_VAL]
Me.chkStateFld = rst![STATE_FLD]
Me.txtStartDate = rst![START_DTE]
Me.txtEndDate = rst![END_DTE]
End If
'Used later for data validation
iMaxCodeLen = rst.Fields("COUNTYCODE").Size
iMaxNameLen = rst.Fields("DESCRIPTION").Size
iMaxValLen = rst.Fields("STATE_VAL").Size

rst.Close
Set rst = Nothing

End Function

ccxc2003 said:
I have 2 databases i am working with, the first is an oracle database from
which i am filling out a form to then put data in an access database.
However, i can not figure out how to get the contents of the query
(recordset) to appear in all the text boxes on my form. So far i am using a
text box with a query button. When i click the query button the results
appear in the table that comes up, but i can not no matter what i try get
them to show up in the text boxes. Any ideas would be of GREAT help.
Click to expand...
Click to expand...
Click to expand...
 
Sorry for the confusion, thanks again for the advice.

This dlookup seems to be working quite well actually (now) and i am
currently trying to figure out how to do an if exist check to stop the
program from crashing if false.


Thanks again.

Klatuu said:
I don't understand what you mean by "My fields are bound to the access
database".
Fields are bound to controls on a form, but not to the database itself. A
bound form means that you have identified a Row Source for your form. You
then bind fields from the Row Source (a table or query) to controls on the
form. This is the most common way to get the field data to display on your
form. Each time you select a new record, the field data is loaded into the
form control it is bound to. I think in your case, you are using an unbound
form, which means the form does not have a row source and you must manipulate
record movement and data display and updating programmatically.
The example I posted is for use with an unbound form. You have more control
over the form's behaviour with unbound forms, but it takes a lot more
programming.
Post your Dlookup code that is not working, and let's see if we can fix that.

ccxc2003 said:
Thanks for the help.


I have since tried to use the Dlookup function, with little success. My
fields are bound to the access database, is this a bad thing? And if so how
do you post the data without binding them?
Klatuu said:
Is your form bound or unbound? Based on your post, I am guessing it is
unbound. If this is correct, then you will have to load the fields into your
form using VBA.
Here is an example of that:

Private Function LoadRecord(strCode As String) As Boolean
Dim rst As Recordset

Set rst = GetTableRecord("qselMtbCounty", strCode)
If rst.RecordCount = 0 Then
LoadRecord = False
Else
LoadRecord = True
Me.txtCode = rst![COUNTYCODE]
Me.txtDisplayDescription = rst![DESCRIPTION]
'FIX - put fields in here for loading controls
Me.chkMetro = rst![METRO]
Me.txtStateVal = rst![STATE_VAL]
Me.chkStateFld = rst![STATE_FLD]
Me.txtStartDate = rst![START_DTE]
Me.txtEndDate = rst![END_DTE]
End If
'Used later for data validation
iMaxCodeLen = rst.Fields("COUNTYCODE").Size
iMaxNameLen = rst.Fields("DESCRIPTION").Size
iMaxValLen = rst.Fields("STATE_VAL").Size

rst.Close
Set rst = Nothing

End Function

:

I have 2 databases i am working with, the first is an oracle database from
which i am filling out a form to then put data in an access database.
However, i can not figure out how to get the contents of the query
(recordset) to appear in all the text boxes on my form. So far i am using a
text box with a query button. When i click the query button the results
appear in the table that comes up, but i can not no matter what i try get
them to show up in the text boxes. Any ideas would be of GREAT help.
Click to expand...
Click to expand...
Click to expand...
 
If what exists?

ccxc2003 said:
Sorry for the confusion, thanks again for the advice.

This dlookup seems to be working quite well actually (now) and i am
currently trying to figure out how to do an if exist check to stop the
program from crashing if false.


Thanks again.

Klatuu said:
I don't understand what you mean by "My fields are bound to the access
database".
Fields are bound to controls on a form, but not to the database itself. A
bound form means that you have identified a Row Source for your form. You
then bind fields from the Row Source (a table or query) to controls on the
form. This is the most common way to get the field data to display on your
form. Each time you select a new record, the field data is loaded into the
form control it is bound to. I think in your case, you are using an unbound
form, which means the form does not have a row source and you must manipulate
record movement and data display and updating programmatically.
The example I posted is for use with an unbound form. You have more control
over the form's behaviour with unbound forms, but it takes a lot more
programming.
Post your Dlookup code that is not working, and let's see if we can fix that.

ccxc2003 said:
Thanks for the help.


I have since tried to use the Dlookup function, with little success. My
fields are bound to the access database, is this a bad thing? And if so how
do you post the data without binding them?
:

Is your form bound or unbound? Based on your post, I am guessing it is
unbound. If this is correct, then you will have to load the fields into your
form using VBA.
Here is an example of that:

Private Function LoadRecord(strCode As String) As Boolean
Dim rst As Recordset

Set rst = GetTableRecord("qselMtbCounty", strCode)
If rst.RecordCount = 0 Then
LoadRecord = False
Else
LoadRecord = True
Me.txtCode = rst![COUNTYCODE]
Me.txtDisplayDescription = rst![DESCRIPTION]
'FIX - put fields in here for loading controls
Me.chkMetro = rst![METRO]
Me.txtStateVal = rst![STATE_VAL]
Me.chkStateFld = rst![STATE_FLD]
Me.txtStartDate = rst![START_DTE]
Me.txtEndDate = rst![END_DTE]
End If
'Used later for data validation
iMaxCodeLen = rst.Fields("COUNTYCODE").Size
iMaxNameLen = rst.Fields("DESCRIPTION").Size
iMaxValLen = rst.Fields("STATE_VAL").Size

rst.Close
Set rst = Nothing

End Function

:

I have 2 databases i am working with, the first is an oracle database from
which i am filling out a form to then put data in an access database.
However, i can not figure out how to get the contents of the query
(recordset) to appear in all the text boxes on my form. So far i am using a
text box with a query button. When i click the query button the results
appear in the table that comes up, but i can not no matter what i try get
them to show up in the text boxes. Any ideas would be of GREAT help.
Click to expand...
Click to expand...
Click to expand...
 
Private Sub txtrvr_LostFocus()
Dim qty As String
Dim cmpnt As String
'If !Forms!Inputtable!txtrvr = Null Then

'cmpnt = 0
'qty = 0

'Else
cmpnt = DLookup("[PART]", "[wdsqry]")
qty = DLookup("[QTY_RCV]", "[wdsqry]")
'If (cmpnt = Null) Then
'MsgBox("Please Re-scan", vbExclamation,,,) As VbMsgBoxResult
'cmpnt = 0
'Else
Me.txtComponentName.Value = cmpnt
Me.txtQuantity.Value = qty
'End If
End Sub


sorry for got to put this up
a test if there is a value returned for the dlookup function.

Klatuu said:
If what exists?

ccxc2003 said:
Sorry for the confusion, thanks again for the advice.

This dlookup seems to be working quite well actually (now) and i am
currently trying to figure out how to do an if exist check to stop the
program from crashing if false.


Thanks again.

Klatuu said:
I don't understand what you mean by "My fields are bound to the access
database".
Fields are bound to controls on a form, but not to the database itself. A
bound form means that you have identified a Row Source for your form. You
then bind fields from the Row Source (a table or query) to controls on the
form. This is the most common way to get the field data to display on your
form. Each time you select a new record, the field data is loaded into the
form control it is bound to. I think in your case, you are using an unbound
form, which means the form does not have a row source and you must manipulate
record movement and data display and updating programmatically.
The example I posted is for use with an unbound form. You have more control
over the form's behaviour with unbound forms, but it takes a lot more
programming.
Post your Dlookup code that is not working, and let's see if we can fix that.

:

Thanks for the help.


I have since tried to use the Dlookup function, with little success. My
fields are bound to the access database, is this a bad thing? And if so how
do you post the data without binding them?
:

Is your form bound or unbound? Based on your post, I am guessing it is
unbound. If this is correct, then you will have to load the fields into your
form using VBA.
Here is an example of that:

Private Function LoadRecord(strCode As String) As Boolean
Dim rst As Recordset

Set rst = GetTableRecord("qselMtbCounty", strCode)
If rst.RecordCount = 0 Then
LoadRecord = False
Else
LoadRecord = True
Me.txtCode = rst![COUNTYCODE]
Me.txtDisplayDescription = rst![DESCRIPTION]
'FIX - put fields in here for loading controls
Me.chkMetro = rst![METRO]
Me.txtStateVal = rst![STATE_VAL]
Me.chkStateFld = rst![STATE_FLD]
Me.txtStartDate = rst![START_DTE]
Me.txtEndDate = rst![END_DTE]
End If
'Used later for data validation
iMaxCodeLen = rst.Fields("COUNTYCODE").Size
iMaxNameLen = rst.Fields("DESCRIPTION").Size
iMaxValLen = rst.Fields("STATE_VAL").Size

rst.Close
Set rst = Nothing

End Function

:

I have 2 databases i am working with, the first is an oracle database from
which i am filling out a form to then put data in an access database.
However, i can not figure out how to get the contents of the query
(recordset) to appear in all the text boxes on my form. So far i am using a
text box with a query button. When i click the query button the results
appear in the table that comes up, but i can not no matter what i try get
them to show up in the text boxes. Any ideas would be of GREAT help.
Click to expand...
Click to expand...
Click to expand...
 
cmpnt = DLookup("[PART]", "[wdsqry]")
If IsNull(cmpnt) Then
'It was not Found
End If

ccxc2003 said:
Private Sub txtrvr_LostFocus()
Dim qty As String
Dim cmpnt As String
'If !Forms!Inputtable!txtrvr = Null Then

'cmpnt = 0
'qty = 0

'Else
cmpnt = DLookup("[PART]", "[wdsqry]")
qty = DLookup("[QTY_RCV]", "[wdsqry]")
'If (cmpnt = Null) Then
'MsgBox("Please Re-scan", vbExclamation,,,) As VbMsgBoxResult
'cmpnt = 0
'Else
Me.txtComponentName.Value = cmpnt
Me.txtQuantity.Value = qty
'End If
End Sub


sorry for got to put this up
a test if there is a value returned for the dlookup function.

Klatuu said:
If what exists?

ccxc2003 said:
Sorry for the confusion, thanks again for the advice.

This dlookup seems to be working quite well actually (now) and i am
currently trying to figure out how to do an if exist check to stop the
program from crashing if false.


Thanks again.

:

I don't understand what you mean by "My fields are bound to the access
database".
Fields are bound to controls on a form, but not to the database itself. A
bound form means that you have identified a Row Source for your form. You
then bind fields from the Row Source (a table or query) to controls on the
form. This is the most common way to get the field data to display on your
form. Each time you select a new record, the field data is loaded into the
form control it is bound to. I think in your case, you are using an unbound
form, which means the form does not have a row source and you must manipulate
record movement and data display and updating programmatically.
The example I posted is for use with an unbound form. You have more control
over the form's behaviour with unbound forms, but it takes a lot more
programming.
Post your Dlookup code that is not working, and let's see if we can fix that.

:

Thanks for the help.


I have since tried to use the Dlookup function, with little success. My
fields are bound to the access database, is this a bad thing? And if so how
do you post the data without binding them?
:

Is your form bound or unbound? Based on your post, I am guessing it is
unbound. If this is correct, then you will have to load the fields into your
form using VBA.
Here is an example of that:

Private Function LoadRecord(strCode As String) As Boolean
Dim rst As Recordset

Set rst = GetTableRecord("qselMtbCounty", strCode)
If rst.RecordCount = 0 Then
LoadRecord = False
Else
LoadRecord = True
Me.txtCode = rst![COUNTYCODE]
Me.txtDisplayDescription = rst![DESCRIPTION]
'FIX - put fields in here for loading controls
Me.chkMetro = rst![METRO]
Me.txtStateVal = rst![STATE_VAL]
Me.chkStateFld = rst![STATE_FLD]
Me.txtStartDate = rst![START_DTE]
Me.txtEndDate = rst![END_DTE]
End If
'Used later for data validation
iMaxCodeLen = rst.Fields("COUNTYCODE").Size
iMaxNameLen = rst.Fields("DESCRIPTION").Size
iMaxValLen = rst.Fields("STATE_VAL").Size

rst.Close
Set rst = Nothing

End Function

:

I have 2 databases i am working with, the first is an oracle database from
which i am filling out a form to then put data in an access database.
However, i can not figure out how to get the contents of the query
(recordset) to appear in all the text boxes on my form. So far i am using a
text box with a query button. When i click the query button the results
appear in the table that comes up, but i can not no matter what i try get
them to show up in the text boxes. Any ideas would be of GREAT help.
Click to expand...
Click to expand...
Click to expand...
 
thanks Klatuu, much simpler than what i have been trying.

Klatuu said:
cmpnt = DLookup("[PART]", "[wdsqry]")
If IsNull(cmpnt) Then
'It was not Found
End If

ccxc2003 said:
Private Sub txtrvr_LostFocus()
Dim qty As String
Dim cmpnt As String
'If !Forms!Inputtable!txtrvr = Null Then

'cmpnt = 0
'qty = 0

'Else
cmpnt = DLookup("[PART]", "[wdsqry]")
qty = DLookup("[QTY_RCV]", "[wdsqry]")
'If (cmpnt = Null) Then
'MsgBox("Please Re-scan", vbExclamation,,,) As VbMsgBoxResult
'cmpnt = 0
'Else
Me.txtComponentName.Value = cmpnt
Me.txtQuantity.Value = qty
'End If
End Sub


sorry for got to put this up
a test if there is a value returned for the dlookup function.

Klatuu said:
If what exists?

:

Sorry for the confusion, thanks again for the advice.

This dlookup seems to be working quite well actually (now) and i am
currently trying to figure out how to do an if exist check to stop the
program from crashing if false.


Thanks again.

:

I don't understand what you mean by "My fields are bound to the access
database".
Fields are bound to controls on a form, but not to the database itself. A
bound form means that you have identified a Row Source for your form. You
then bind fields from the Row Source (a table or query) to controls on the
form. This is the most common way to get the field data to display on your
form. Each time you select a new record, the field data is loaded into the
form control it is bound to. I think in your case, you are using an unbound
form, which means the form does not have a row source and you must manipulate
record movement and data display and updating programmatically.
The example I posted is for use with an unbound form. You have more control
over the form's behaviour with unbound forms, but it takes a lot more
programming.
Post your Dlookup code that is not working, and let's see if we can fix that.

:

Thanks for the help.


I have since tried to use the Dlookup function, with little success. My
fields are bound to the access database, is this a bad thing? And if so how
do you post the data without binding them?
:

Is your form bound or unbound? Based on your post, I am guessing it is
unbound. If this is correct, then you will have to load the fields into your
form using VBA.
Here is an example of that:

Private Function LoadRecord(strCode As String) As Boolean
Dim rst As Recordset

Set rst = GetTableRecord("qselMtbCounty", strCode)
If rst.RecordCount = 0 Then
LoadRecord = False
Else
LoadRecord = True
Me.txtCode = rst![COUNTYCODE]
Me.txtDisplayDescription = rst![DESCRIPTION]
'FIX - put fields in here for loading controls
Me.chkMetro = rst![METRO]
Me.txtStateVal = rst![STATE_VAL]
Me.chkStateFld = rst![STATE_FLD]
Me.txtStartDate = rst![START_DTE]
Me.txtEndDate = rst![END_DTE]
End If
'Used later for data validation
iMaxCodeLen = rst.Fields("COUNTYCODE").Size
iMaxNameLen = rst.Fields("DESCRIPTION").Size
iMaxValLen = rst.Fields("STATE_VAL").Size

rst.Close
Set rst = Nothing

End Function

:

I have 2 databases i am working with, the first is an oracle database from
which i am filling out a form to then put data in an access database.
However, i can not figure out how to get the contents of the query
(recordset) to appear in all the text boxes on my form. So far i am using a
text box with a query button. When i click the query button the results
appear in the table that comes up, but i can not no matter what i try get
them to show up in the text boxes. Any ideas would be of GREAT help.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top