I'm Just Learning Question

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

Guest

I created a form with a textbox that get a auto-number from the user. The
auto-number is the Primary Key. I want to go to the table and select this
record. I wrote a query that works using “[]†to ask which number. My
thinking was that I could view the “SQL†and learn from that. Wrong!!! Now
I’m more confused that ever. Can someone lead me in the correct direction?
 
Although what you are suggesting would work, that is, to have the user
provide the record ID number. It is asking a lot of any user to try to
remember the autogenerated ID number.

Normally what would be done would be to provide a combo box or a list box
with the appropriate information from the table throught the use of a record
selection query for the list box or combo box, including the ID number (most
of the time the ID number would be the first field in the query (but not
displayed in the list box or combo box). Then in the AfterUpdate event of the
list or combo box, you would simply get the value from the list or combo box
and use this value as the value to locate the record your user wanted to see.


Using this method, the user would see data in the list or combo box that
would be meaningful and not just a non-meaningful auto-generated ID number.

Look in the Access Help file for info on using a combo box or a list box.
 
Create a form that is bound to your table. The simplest way to find and
display a particular record is to put the cursor into the text box that
shows the primary key, and click the Binocular icon on the toolbar (or
choose Find on the Edit menu.)

A better solution might be to add an unbound text box to the top of your
form. Use its AfterUpdate event to find and display the matching number. The
code to use is identical to this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
You can ignore the instructions on how to set up the combo it you want to
use a text box, but the code is the same either way.
 
OK. Here is what I did. I created a query that ask me for the info. I
enter it using the "[]" in that field. It works fine. I want to make the
same query work using a command button on a form, so I created the form, I
ask for the info in the txtemployee text box. I coded it as follows:

Private Sub cmdContinue_Click()
Dim EmployeeNo As Integer
EmployeeNo = Me.txtEmployee

'Run Query to get Employee information
DoCmd.OpenQuery ("qryFindEmployee")

In the query, in the employee# field I have the following:
=forms"[frmEmployeeRelease]![EmployeeNO]"

What I expect is for the query to search in the EmployeeNo field for the
number "EmployeeNo" but what I get is an error message saying I cancelled the
event. I don't see the error of my ways. I put in a debug print, to make
sure I was getting a number and that was correct.

Allen Browne said:
Create a form that is bound to your table. The simplest way to find and
display a particular record is to put the cursor into the text box that
shows the primary key, and click the Binocular icon on the toolbar (or
choose Find on the Edit menu.)

A better solution might be to add an unbound text box to the top of your
form. Use its AfterUpdate event to find and display the matching number. The
code to use is identical to this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
You can ignore the instructions on how to set up the combo it you want to
use a text box, but the code is the same either way.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

williamr said:
I created a form with a textbox that get a auto-number from the user. The
auto-number is the Primary Key. I want to go to the table and select this
record. I wrote a query that works using "[]" to ask which number. My
thinking was that I could view the "SQL" and learn from that. Wrong!!!
Now
I'm more confused that ever. Can someone lead me in the correct
direction?
Click to expand...
Click to expand...
 
Try:
Forms![frmEmployeeRelease]![EmployeeNO]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

williamr said:
OK. Here is what I did. I created a query that ask me for the info. I
enter it using the "[]" in that field. It works fine. I want to make the
same query work using a command button on a form, so I created the form, I
ask for the info in the txtemployee text box. I coded it as follows:

Private Sub cmdContinue_Click()
Dim EmployeeNo As Integer
EmployeeNo = Me.txtEmployee

'Run Query to get Employee information
DoCmd.OpenQuery ("qryFindEmployee")

In the query, in the employee# field I have the following:
=forms"[frmEmployeeRelease]![EmployeeNO]"

What I expect is for the query to search in the EmployeeNo field for the
number "EmployeeNo" but what I get is an error message saying I cancelled
the
event. I don't see the error of my ways. I put in a debug print, to make
sure I was getting a number and that was correct.

Allen Browne said:
Create a form that is bound to your table. The simplest way to find and
display a particular record is to put the cursor into the text box that
shows the primary key, and click the Binocular icon on the toolbar (or
choose Find on the Edit menu.)

A better solution might be to add an unbound text box to the top of your
form. Use its AfterUpdate event to find and display the matching number.
The
code to use is identical to this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
You can ignore the instructions on how to set up the combo it you want to
use a text box, but the code is the same either way.

williamr said:
I created a form with a textbox that get a auto-number from the user.
The
auto-number is the Primary Key. I want to go to the table and select
this
record. I wrote a query that works using "[]" to ask which number. My
thinking was that I could view the "SQL" and learn from that. Wrong!!!
Now
I'm more confused that ever. Can someone lead me in the correct
direction?
Click to expand...
Click to expand...
Click to expand...
 
My frustration is @ an all time high. I'm getting the message "Can't find
the project! What does that mean? This is harder than my accounting
classes!!! I will post what I'm trying to do if you want to take a look!

Thank You

Private Sub cmdContinue_Click()
Dim EmployeeNo As Integer
EmployeeNo = Me.txtWhichEmployee
'Employee To Be Released
Debug.Print "Going to Release " & EmployeeNo

'Run Query to get Employee information
'DoCmd.OpenQuery (qryReleaseEmployee)

Then in the employee Field of the query
Forms![frmEmployeeRelease]![EmployeeNO]

Allen Browne said:
Try:
Forms![frmEmployeeRelease]![EmployeeNO]

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

williamr said:
OK. Here is what I did. I created a query that ask me for the info. I
enter it using the "[]" in that field. It works fine. I want to make the
same query work using a command button on a form, so I created the form, I
ask for the info in the txtemployee text box. I coded it as follows:

Private Sub cmdContinue_Click()
Dim EmployeeNo As Integer
EmployeeNo = Me.txtEmployee

'Run Query to get Employee information
DoCmd.OpenQuery ("qryFindEmployee")

In the query, in the employee# field I have the following:
=forms"[frmEmployeeRelease]![EmployeeNO]"

What I expect is for the query to search in the EmployeeNo field for the
number "EmployeeNo" but what I get is an error message saying I cancelled
the
event. I don't see the error of my ways. I put in a debug print, to make
sure I was getting a number and that was correct.

Allen Browne said:
Create a form that is bound to your table. The simplest way to find and
display a particular record is to put the cursor into the text box that
shows the primary key, and click the Binocular icon on the toolbar (or
choose Find on the Edit menu.)

A better solution might be to add an unbound text box to the top of your
form. Use its AfterUpdate event to find and display the matching number.
The
code to use is identical to this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
You can ignore the instructions on how to set up the combo it you want to
use a text box, but the code is the same either way.

I created a form with a textbox that get a auto-number from the user.
The
auto-number is the Primary Key. I want to go to the table and select
this
record. I wrote a query that works using "[]" to ask which number. My
thinking was that I could view the "SQL" and learn from that. Wrong!!!
Now
I'm more confused that ever. Can someone lead me in the correct
direction?
Click to expand...
Click to expand...
Click to expand...
 
The "project" is the entire set of modules (code). Perhaps you have
something else with the same name as the project, and this has Access
confused.

Open a code window. On the Tools, choose:
xxxx Options
where xxxx represents the name of your project. You can use that dialog to
rename the project to something that will not clash.

If that is not the case, there may be a corruption in the project. Try this
sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
and the code syntax is compilable.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"open a adobe file from a command button"
My frustration is @ an all time high. I'm getting the message "Can't find
the project! What does that mean? This is harder than my accounting
classes!!! I will post what I'm trying to do if you want to take a look!

Thank You

Private Sub cmdContinue_Click()
Dim EmployeeNo As Integer
EmployeeNo = Me.txtWhichEmployee
'Employee To Be Released
Debug.Print "Going to Release " & EmployeeNo

'Run Query to get Employee information
'DoCmd.OpenQuery (qryReleaseEmployee)

Then in the employee Field of the query
Forms![frmEmployeeRelease]![EmployeeNO]

Allen Browne said:
Try:
Forms![frmEmployeeRelease]![EmployeeNO]

williamr said:
OK. Here is what I did. I created a query that ask me for the info.
I
enter it using the "[]" in that field. It works fine. I want to make
the
same query work using a command button on a form, so I created the
form, I
ask for the info in the txtemployee text box. I coded it as follows:

Private Sub cmdContinue_Click()
Dim EmployeeNo As Integer
EmployeeNo = Me.txtEmployee

'Run Query to get Employee information
DoCmd.OpenQuery ("qryFindEmployee")

In the query, in the employee# field I have the following:
=forms"[frmEmployeeRelease]![EmployeeNO]"

What I expect is for the query to search in the EmployeeNo field for
the
number "EmployeeNo" but what I get is an error message saying I
cancelled
the
event. I don't see the error of my ways. I put in a debug print, to
make
sure I was getting a number and that was correct.

:

Create a form that is bound to your table. The simplest way to find
and
display a particular record is to put the cursor into the text box
that
shows the primary key, and click the Binocular icon on the toolbar (or
choose Find on the Edit menu.)

A better solution might be to add an unbound text box to the top of
your
form. Use its AfterUpdate event to find and display the matching
number.
The
code to use is identical to this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
You can ignore the instructions on how to set up the combo it you want
to
use a text box, but the code is the same either way.

I created a form with a textbox that get a auto-number from the user.
The
auto-number is the Primary Key. I want to go to the table and
select
this
record. I wrote a query that works using "[]" to ask which number.
My
thinking was that I could view the "SQL" and learn from that.
Wrong!!!
Now
I'm more confused that ever. Can someone lead me in the correct
direction?
Click to expand...
Click to expand...
Click to expand...
 
Allen, Hi. Yes I started over because I did have some problems. I'm working
them and after I get this in order I'm sure I will have other questions.
Thanks for your guidance!!!

Allen Browne said:
The "project" is the entire set of modules (code). Perhaps you have
something else with the same name as the project, and this has Access
confused.

Open a code window. On the Tools, choose:
xxxx Options
where xxxx represents the name of your project. You can use that dialog to
rename the project to something that will not clash.

If that is not the case, there may be a corruption in the project. Try this
sequence:

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access, and compact again.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
and the code syntax is compilable.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

"open a adobe file from a command button"
My frustration is @ an all time high. I'm getting the message "Can't find
the project! What does that mean? This is harder than my accounting
classes!!! I will post what I'm trying to do if you want to take a look!

Thank You

Private Sub cmdContinue_Click()
Dim EmployeeNo As Integer
EmployeeNo = Me.txtWhichEmployee
'Employee To Be Released
Debug.Print "Going to Release " & EmployeeNo

'Run Query to get Employee information
'DoCmd.OpenQuery (qryReleaseEmployee)

Then in the employee Field of the query
Forms![frmEmployeeRelease]![EmployeeNO]

Allen Browne said:
Try:
Forms![frmEmployeeRelease]![EmployeeNO]

OK. Here is what I did. I created a query that ask me for the info.
I
enter it using the "[]" in that field. It works fine. I want to make
the
same query work using a command button on a form, so I created the
form, I
ask for the info in the txtemployee text box. I coded it as follows:

Private Sub cmdContinue_Click()
Dim EmployeeNo As Integer
EmployeeNo = Me.txtEmployee

'Run Query to get Employee information
DoCmd.OpenQuery ("qryFindEmployee")

In the query, in the employee# field I have the following:
=forms"[frmEmployeeRelease]![EmployeeNO]"

What I expect is for the query to search in the EmployeeNo field for
the
number "EmployeeNo" but what I get is an error message saying I
cancelled
the
event. I don't see the error of my ways. I put in a debug print, to
make
sure I was getting a number and that was correct.

:

Create a form that is bound to your table. The simplest way to find
and
display a particular record is to put the cursor into the text box
that
shows the primary key, and click the Binocular icon on the toolbar (or
choose Find on the Edit menu.)

A better solution might be to add an unbound text box to the top of
your
form. Use its AfterUpdate event to find and display the matching
number.
The
code to use is identical to this:
Using a Combo Box to Find Records
at:
http://allenbrowne.com/ser-03.html
You can ignore the instructions on how to set up the combo it you want
to
use a text box, but the code is the same either way.

I created a form with a textbox that get a auto-number from the user.
The
auto-number is the Primary Key. I want to go to the table and
select
this
record. I wrote a query that works using "[]" to ask which number.
My
thinking was that I could view the "SQL" and learn from that.
Wrong!!!
Now
I'm more confused that ever. Can someone lead me in the correct
direction?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top