populating a field two different ways?

[Guest]

I've got a main form, where user's can enter an Employee Name OR an Employee
Number. The other fields on the form, such as company, department, etc., as
well as the other criteria field, need to be automatically populated based on
what is entered in either the employee name or employee number fields.

So, if employee Name is entered by the user, Employee ID, department and
company needs to auto-populate. If employee ID is entered, Employee Name,
department and company needs to auto-populate.

I'm thinking that I'll have to write some code that says something like, if
Len(Me![EmployeeID]) = 0 then
Me![EmployeeID]= DoCmd.runsql "Select...
Me![Deparment]=DoCmd.runsql "Select...
Me![Company]=DoCmd.runsql "Select...
else if
If Len(Me![EmployeeName]) = 0 then
.....
End if

Am I complicating this? There has to be a simpler way. Thanks for your help!

 

[strive4peace]

Combobox Example
---

Hi Mark,

one of the beauties of Access is that data need only be stored in one
place -- except, of course, for your key fields.

I am assuming that you have an Employees table with an autonumber
EmployeeID primary key. In the related table, you should be storing
EmployeeID as a long integer. Name, company, department, etc would be
displayed (not repeated). Here is an analogy for you

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I've got a main form, where user's can enter an Employee Name OR an Employee
Number. The other fields on the form, such as company, department, etc., as
well as the other criteria field, need to be automatically populated based on
what is entered in either the employee name or employee number fields.

So, if employee Name is entered by the user, Employee ID, department and
company needs to auto-populate. If employee ID is entered, Employee Name,
department and company needs to auto-populate.

I'm thinking that I'll have to write some code that says something like, if
Len(Me![EmployeeID]) = 0 then
Me![EmployeeID]= DoCmd.runsql "Select...
Me![Deparment]=DoCmd.runsql "Select...
Me![Company]=DoCmd.runsql "Select...
else if
If Len(Me![EmployeeName]) = 0 then
....
End if

Am I complicating this? There has to be a simpler way. Thanks for your help!

 

[Guest]

Thanks for your help! Actually, I've only got one table with ID in one field
and Name in another. If the user doesn't know ID, then they can pull down
the Name combo box and if the user doesn't know name, then they can pull down
the ID combo box. The problem is populating the other fields on the form.
What I've done is created a test textbox thats control source is like this:

IIF( ID_Combobox = "", Name_Query, ID_Query)
So, what is supposed to happen, is if the ID_Combobox is blank, then a query
is run that returns Name, else if ID_Combobox is not blank, another query
runs that returns ID . I have both of those queries saved, and have run them
individually, and they both work. However, this IIF statement does not work.
It gives me a #Name? error. I must also mention that this test text box is

Combobox Example
---

Hi Mark,

one of the beauties of Access is that data need only be stored in one
place -- except, of course, for your key fields.

I am assuming that you have an Employees table with an autonumber
EmployeeID primary key. In the related table, you should be storing
EmployeeID as a long integer. Name, company, department, etc would be
displayed (not repeated). Here is an analogy for you

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I've got a main form, where user's can enter an Employee Name OR an Employee
Number. The other fields on the form, such as company, department, etc., as
well as the other criteria field, need to be automatically populated based on
what is entered in either the employee name or employee number fields.

So, if employee Name is entered by the user, Employee ID, department and
company needs to auto-populate. If employee ID is entered, Employee Name,
department and company needs to auto-populate.

I'm thinking that I'll have to write some code that says something like, if
Len(Me![EmployeeID]) = 0 then
Me![EmployeeID]= DoCmd.runsql "Select...
Me![Deparment]=DoCmd.runsql "Select...
Me![Company]=DoCmd.runsql "Select...
else if
If Len(Me![EmployeeName]) = 0 then
....
End if

Am I complicating this? There has to be a simpler way. Thanks for your help!

 

[strive4peace]

Hi Mark,

you're welcome

did you understand the example I gave you? Even though the user would
be choosing a name, the ID is the bound column...

you are making this much more complicated than it needs to be...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for your help! Actually, I've only got one table with ID in one field
and Name in another. If the user doesn't know ID, then they can pull down
the Name combo box and if the user doesn't know name, then they can pull down
the ID combo box. The problem is populating the other fields on the form.
What I've done is created a test textbox thats control source is like this:

IIF( ID_Combobox = "", Name_Query, ID_Query)
So, what is supposed to happen, is if the ID_Combobox is blank, then a query
is run that returns Name, else if ID_Combobox is not blank, another query
runs that returns ID . I have both of those queries saved, and have run them
individually, and they both work. However, this IIF statement does not work.
It gives me a #Name? error. I must also mention that this test text box is

Combobox Example
---

Hi Mark,

one of the beauties of Access is that data need only be stored in one
place -- except, of course, for your key fields.

I am assuming that you have an Employees table with an autonumber
EmployeeID primary key. In the related table, you should be storing
EmployeeID as a long integer. Name, company, department, etc would be
displayed (not repeated). Here is an analogy for you

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

I've got a main form, where user's can enter an Employee Name OR an Employee
Number. The other fields on the form, such as company, department, etc., as
well as the other criteria field, need to be automatically populated based on
what is entered in either the employee name or employee number fields.

So, if employee Name is entered by the user, Employee ID, department and
company needs to auto-populate. If employee ID is entered, Employee Name,
department and company needs to auto-populate.

I'm thinking that I'll have to write some code that says something like, if
Len(Me![EmployeeID]) = 0 then
Me![EmployeeID]= DoCmd.runsql "Select...
Me![Deparment]=DoCmd.runsql "Select...
Me![Company]=DoCmd.runsql "Select...
else if
If Len(Me![EmployeeName]) = 0 then
....
End if

Am I complicating this? There has to be a simpler way. Thanks for your help!

 

[Guest]

I think that it is starting to get through now that I've read through it a
couple of times. I'm going to have to revamp my forms. I'll give it a try
and let you know how it goes. Thanks!

PS - I still don't understand why my IIF isn't working.

Hi Mark,

you're welcome

did you understand the example I gave you? Even though the user would
be choosing a name, the ID is the bound column...

you are making this much more complicated than it needs to be...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for your help! Actually, I've only got one table with ID in one field
and Name in another. If the user doesn't know ID, then they can pull down
the Name combo box and if the user doesn't know name, then they can pull down
the ID combo box. The problem is populating the other fields on the form.
What I've done is created a test textbox thats control source is like this:

IIF( ID_Combobox = "", Name_Query, ID_Query)
So, what is supposed to happen, is if the ID_Combobox is blank, then a query
is run that returns Name, else if ID_Combobox is not blank, another query
runs that returns ID . I have both of those queries saved, and have run them
individually, and they both work. However, this IIF statement does not work.
It gives me a #Name? error. I must also mention that this test text box is

Combobox Example
---

Hi Mark,

one of the beauties of Access is that data need only be stored in one
place -- except, of course, for your key fields.

I am assuming that you have an Employees table with an autonumber
EmployeeID primary key. In the related table, you should be storing
EmployeeID as a long integer. Name, company, department, etc would be
displayed (not repeated). Here is an analogy for you

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mark1 wrote:
I've got a main form, where user's can enter an Employee Name OR an Employee
Number. The other fields on the form, such as company, department, etc., as
well as the other criteria field, need to be automatically populated based on
what is entered in either the employee name or employee number fields.

So, if employee Name is entered by the user, Employee ID, department and
company needs to auto-populate. If employee ID is entered, Employee Name,
department and company needs to auto-populate.

I'm thinking that I'll have to write some code that says something like, if
Len(Me![EmployeeID]) = 0 then
Me![EmployeeID]= DoCmd.runsql "Select...
Me![Deparment]=DoCmd.runsql "Select...
Me![Company]=DoCmd.runsql "Select...
else if
If Len(Me![EmployeeName]) = 0 then
....
End if

Am I complicating this? There has to be a simpler way. Thanks for your help!

 

[Guest]

Maybe I'm still not getting it, but I'm not seeing how your solution would
help me. Specifically, if I have combo box A with items 1 and 2 in it's
list. Then I have combo box B with items 3 and 4 in it's list. What I want
is for text box C to be populated with 2 from A if B is blank OR 4 from B if
A is blank. See, I don't see how you can say "textbox C ControlSource =
A.column(2) or B.column(4)." Maybe if I use concatenation? So "textbox C
ControlSource = A.column(2) & B.column(4)"?

Hi Mark,

you're welcome

did you understand the example I gave you? Even though the user would
be choosing a name, the ID is the bound column...

you are making this much more complicated than it needs to be...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for your help! Actually, I've only got one table with ID in one field
and Name in another. If the user doesn't know ID, then they can pull down
the Name combo box and if the user doesn't know name, then they can pull down
the ID combo box. The problem is populating the other fields on the form.
What I've done is created a test textbox thats control source is like this:

IIF( ID_Combobox = "", Name_Query, ID_Query)
So, what is supposed to happen, is if the ID_Combobox is blank, then a query
is run that returns Name, else if ID_Combobox is not blank, another query
runs that returns ID . I have both of those queries saved, and have run them
individually, and they both work. However, this IIF statement does not work.
It gives me a #Name? error. I must also mention that this test text box is

Combobox Example
---

Hi Mark,

one of the beauties of Access is that data need only be stored in one
place -- except, of course, for your key fields.

I am assuming that you have an Employees table with an autonumber
EmployeeID primary key. In the related table, you should be storing
EmployeeID as a long integer. Name, company, department, etc would be
displayed (not repeated). Here is an analogy for you

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mark1 wrote:
I've got a main form, where user's can enter an Employee Name OR an Employee
Number. The other fields on the form, such as company, department, etc., as
well as the other criteria field, need to be automatically populated based on
what is entered in either the employee name or employee number fields.

So, if employee Name is entered by the user, Employee ID, department and
company needs to auto-populate. If employee ID is entered, Employee Name,
department and company needs to auto-populate.

I'm thinking that I'll have to write some code that says something like, if
Len(Me![EmployeeID]) = 0 then
Me![EmployeeID]= DoCmd.runsql "Select...
Me![Deparment]=DoCmd.runsql "Select...
Me![Company]=DoCmd.runsql "Select...
else if
If Len(Me![EmployeeName]) = 0 then
....
End if

Am I complicating this? There has to be a simpler way. Thanks for your help!

 

[strive4peace]

Hi Mark,

In order to help you, we need to know your data structure. Here is
something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Maybe I'm still not getting it, but I'm not seeing how your solution would
help me. Specifically, if I have combo box A with items 1 and 2 in it's
list. Then I have combo box B with items 3 and 4 in it's list. What I want
is for text box C to be populated with 2 from A if B is blank OR 4 from B if
A is blank. See, I don't see how you can say "textbox C ControlSource =
A.column(2) or B.column(4)." Maybe if I use concatenation? So "textbox C
ControlSource = A.column(2) & B.column(4)"?

Hi Mark,

you're welcome

did you understand the example I gave you? Even though the user would
be choosing a name, the ID is the bound column...

you are making this much more complicated than it needs to be...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Thanks for your help! Actually, I've only got one table with ID in one field
and Name in another. If the user doesn't know ID, then they can pull down
the Name combo box and if the user doesn't know name, then they can pull down
the ID combo box. The problem is populating the other fields on the form.
What I've done is created a test textbox thats control source is like this:

IIF( ID_Combobox = "", Name_Query, ID_Query)
So, what is supposed to happen, is if the ID_Combobox is blank, then a query
is run that returns Name, else if ID_Combobox is not blank, another query
runs that returns ID . I have both of those queries saved, and have run them
individually, and they both work. However, this IIF statement does not work.
It gives me a #Name? error. I must also mention that this test text box is
on another form that is opened when the user enters either ID or Name on Form
1 and hits a command button. Any ideas why this IIF isn't working?

:

Combobox Example
---

Hi Mark,

one of the beauties of Access is that data need only be stored in one
place -- except, of course, for your key fields.

I am assuming that you have an Employees table with an autonumber
EmployeeID primary key. In the related table, you should be storing
EmployeeID as a long integer. Name, company, department, etc would be
displayed (not repeated). Here is an analogy for you

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mark1 wrote:
I've got a main form, where user's can enter an Employee Name OR an Employee
Number. The other fields on the form, such as company, department, etc., as
well as the other criteria field, need to be automatically populated based on
what is entered in either the employee name or employee number fields.

So, if employee Name is entered by the user, Employee ID, department and
company needs to auto-populate. If employee ID is entered, Employee Name,
department and company needs to auto-populate.

I'm thinking that I'll have to write some code that says something like, if
Len(Me![EmployeeID]) = 0 then
Me![EmployeeID]= DoCmd.runsql "Select...
Me![Deparment]=DoCmd.runsql "Select...
Me![Company]=DoCmd.runsql "Select...
else if
If Len(Me![EmployeeName]) = 0 then
....
End if

Am I complicating this? There has to be a simpler way. Thanks for your help!

 

[Guest]

Hey Crystal,
I used your original solution with a combination of my IIF statement and
came up with a solution. Here's what I did. The data for both of the combo
boxes came from one table. I made them include all of the columns that I
needed for other controls and hid those columns. Then, in my text box I put:

IIF(combobox1 = "", combobox2.column(2) , combobox1.column(2))

And it worked!!! Thank you so much for taking the time to help!

Hi Mark,

In order to help you, we need to know your data structure. Here is
something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Maybe I'm still not getting it, but I'm not seeing how your solution would
help me. Specifically, if I have combo box A with items 1 and 2 in it's
list. Then I have combo box B with items 3 and 4 in it's list. What I want
is for text box C to be populated with 2 from A if B is blank OR 4 from B if
A is blank. See, I don't see how you can say "textbox C ControlSource =
A.column(2) or B.column(4)." Maybe if I use concatenation? So "textbox C
ControlSource = A.column(2) & B.column(4)"?

Hi Mark,

you're welcome

did you understand the example I gave you? Even though the user would
be choosing a name, the ID is the bound column...

you are making this much more complicated than it needs to be...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mark1 wrote:
Thanks for your help! Actually, I've only got one table with ID in one field
and Name in another. If the user doesn't know ID, then they can pull down
the Name combo box and if the user doesn't know name, then they can pull down
the ID combo box. The problem is populating the other fields on the form.
What I've done is created a test textbox thats control source is like this:

IIF( ID_Combobox = "", Name_Query, ID_Query)
So, what is supposed to happen, is if the ID_Combobox is blank, then a query
is run that returns Name, else if ID_Combobox is not blank, another query
runs that returns ID . I have both of those queries saved, and have run them
individually, and they both work. However, this IIF statement does not work.
It gives me a #Name? error. I must also mention that this test text box is
on another form that is opened when the user enters either ID or Name on Form
1 and hits a command button. Any ideas why this IIF isn't working?

:

Combobox Example
---

Hi Mark,

one of the beauties of Access is that data need only be stored in one
place -- except, of course, for your key fields.

I am assuming that you have an Employees table with an autonumber
EmployeeID primary key. In the related table, you should be storing
EmployeeID as a long integer. Name, company, department, etc would be
displayed (not repeated). Here is an analogy for you

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mark1 wrote:
I've got a main form, where user's can enter an Employee Name OR an Employee
Number. The other fields on the form, such as company, department, etc., as
well as the other criteria field, need to be automatically populated based on
what is entered in either the employee name or employee number fields.

So, if employee Name is entered by the user, Employee ID, department and
company needs to auto-populate. If employee ID is entered, Employee Name,
department and company needs to auto-populate.

I'm thinking that I'll have to write some code that says something like, if
Len(Me![EmployeeID]) = 0 then
Me![EmployeeID]= DoCmd.runsql "Select...
Me![Deparment]=DoCmd.runsql "Select...
Me![Company]=DoCmd.runsql "Select...
else if
If Len(Me![EmployeeName]) = 0 then
....
End if

Am I complicating this? There has to be a simpler way. Thanks for your help!

 

[strive4peace]

you're welcome, Mark

I would suggest a change, though... instead of -->
IIF(combobox1 = "",
do -->
IIF(len(trim(nz(me.combobox1,""))) = 0,

It is a good idea to get used to typeing 'me.' in your code so that you
are prompted with valid choices (not everything you can pick is on the
list, but your controlnames will be there)

it is also a good idea to change the NAME property of your controls to
something logical so your code is easier to understand

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Hey Crystal,
I used your original solution with a combination of my IIF statement and
came up with a solution. Here's what I did. The data for both of the combo
boxes came from one table. I made them include all of the columns that I
needed for other controls and hid those columns. Then, in my text box I put:

IIF(combobox1 = "", combobox2.column(2) , combobox1.column(2))

And it worked!!! Thank you so much for taking the time to help!

Hi Mark,

In order to help you, we need to know your data structure. Here is
something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*

Maybe I'm still not getting it, but I'm not seeing how your solution would
help me. Specifically, if I have combo box A with items 1 and 2 in it's
list. Then I have combo box B with items 3 and 4 in it's list. What I want
is for text box C to be populated with 2 from A if B is blank OR 4 from B if
A is blank. See, I don't see how you can say "textbox C ControlSource =
A.column(2) or B.column(4)." Maybe if I use concatenation? So "textbox C
ControlSource = A.column(2) & B.column(4)"?


:

Hi Mark,

you're welcome

did you understand the example I gave you? Even though the user would
be choosing a name, the ID is the bound column...

you are making this much more complicated than it needs to be...


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mark1 wrote:
Thanks for your help! Actually, I've only got one table with ID in one field
and Name in another. If the user doesn't know ID, then they can pull down
the Name combo box and if the user doesn't know name, then they can pull down
the ID combo box. The problem is populating the other fields on the form.
What I've done is created a test textbox thats control source is like this:

IIF( ID_Combobox = "", Name_Query, ID_Query)
So, what is supposed to happen, is if the ID_Combobox is blank, then a query
is run that returns Name, else if ID_Combobox is not blank, another query
runs that returns ID . I have both of those queries saved, and have run them
individually, and they both work. However, this IIF statement does not work.
It gives me a #Name? error. I must also mention that this test text box is
on another form that is opened when the user enters either ID or Name on Form
1 and hits a command button. Any ideas why this IIF isn't working?

:

Combobox Example
---

Hi Mark,

one of the beauties of Access is that data need only be stored in one
place -- except, of course, for your key fields.

I am assuming that you have an Employees table with an autonumber
EmployeeID primary key. In the related table, you should be storing
EmployeeID as a long integer. Name, company, department, etc would be
displayed (not repeated). Here is an analogy for you

Combobox Example

* Under no circumstances should you store names in more than one place.
For instance, if you have a People table, define a PID (or PeopleID)
autonumber field. Then, in other tables, when you want to identify a
person, you can use the key field. One way to do this…

Create an autonumber field in the People table -->

PID, autonumber

then, in the other tables...
PID, long, DefaultValue = Null

Then, when you want to put data in (which should be done from a form),
you can set it up to pick names from a list but store the PID.

create a combobox control

Name --> PID

ControlSource --> PID

RowSource -->
SELECT
PID,
LastName & ", " & Firstname AS Fullname,
BirthDate
FROM People
ORDER BY LastName, Firstname

BoundColumn --> 1

ColumnCount --> 3

columnWidths --> 0;2;1
(etc for however many columns you have -- the ID column will be hidden)

ListWidth --> 3
(should add up to the sum of the column widths)

if you have a listbox, sometimes you need to make the width .01 more
than the sum of the columns to prevent the horizontal scrollbar from
appearing.

PID will be stored in the form RecordSource while showing you names from
another table... a MUCH better and more reliable method.

If you want to show other information from your combobox in other
controls, you can use calculated fields. For instance

textbox:
Name --> BirthDate
ControlSource --> = PID.column(2)

The reason that column 2 is referenced instead of column 3 is that
column indexes start with 0, not 1, in Access.


Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Mark1 wrote:
I've got a main form, where user's can enter an Employee Name OR an Employee
Number. The other fields on the form, such as company, department, etc., as
well as the other criteria field, need to be automatically populated based on
what is entered in either the employee name or employee number fields.

So, if employee Name is entered by the user, Employee ID, department and
company needs to auto-populate. If employee ID is entered, Employee Name,
department and company needs to auto-populate.

I'm thinking that I'll have to write some code that says something like, if
Len(Me![EmployeeID]) = 0 then
Me![EmployeeID]= DoCmd.runsql "Select...
Me![Deparment]=DoCmd.runsql "Select...
Me![Company]=DoCmd.runsql "Select...
else if
If Len(Me![EmployeeName]) = 0 then
....
End if

Am I complicating this? There has to be a simpler way. Thanks for your help!