Solved

combo box + enter key

Posted on 2001-07-03
29
411 Views
Last Modified: 2008-02-07
hi,

I would like to know that is that possible for me to key in a data in combo box and after I press "enter", if the data is in the access table, then, it will retrieve and display the records in other combo box and text box that are related to that data in the same table.
but if the data is not in the access table, then it will add the data in the access table by asking the user to key in other data that are related.

how can I write the code for this function?
0
Comment
Question by:s_ling
  • 14
  • 8
  • 7
29 Comments
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi s_ling, you can use the SQL like this:

"Select fields1, fields2 from table where fields1 = '" & cmobo1.text & "'"

if NoRecordWasFound then
   prompt use to CreateNewRecord
else
   ShowExactRecord
endif

'The way to use depends on what method you use..
0
 

Author Comment

by:s_ling
Comment Utility
hi ryancys,

can you give me an example?
isn't that select function is for dbgrid?

here is my code
 If Combo1.Text = "" Then
    MsgBox "you must key in the data"
    DatPecref.Recordset.Edit
    DatPecref.Recordset.CancelUpdate
    End If
   
 
   With DatPecref.Recordset
        .FindFirst "PECREFNO='" & Combo1 & "'"
    If .NoMatch = True Then
        GoTo NotFound
    Else
        GoTo FoundIt
    End If
   
   
NotFound:

    DatPecref.Recordset.Update
    cmdOk.Enabled = False
    MsgBox "data saved"
    DatPecref.Refresh
    DBGRID.Enabled = True

   
FoundIt:
    DatPecref.Recordset.CancelUpdate
    DatPecref.Recordset.Fields("PECREFNO").Value = Combo1
    DatPecref.Recordset.Fields("ACCOUNT").Value = Combo2
    DatPecref.Recordset.Fields("LISENS1").Value = Combo3
    DatPecref.Recordset.Fields("MPLAN1").Value = txtmanagement
    DatPecref.Recordset.Fields("MPLAN2").Value = txtPlan
    DatPecref.Recordset.Fields("ISSUDATE").Value = txtIssue
    SQL = "SELECT INVDATE,BLKNO,BLKAREA,STRIPNO,STRIPLEN,TEAM,REMARK,ATTACH FROM INVFILE WHERE PECREFNO = '" & Combo1.Text & "'"
       DatInvfile.RecordSource = SQL
       DatInvfile.Refresh
   
   
  End With
  End If
0
 

Author Comment

by:s_ling
Comment Utility
the
SQL = "SELECT INVDATE,BLKNO,BLKAREA,STRIPNO,STRIPLEN,TEAM,REMARK,ATTACH FROM INVFILE WHERE PECREFNO = '" & Combo1.Text & "'"
      DatInvfile.RecordSource = SQL
      DatInvfile.Refresh

is for the dbgrid below the table with combo box and text box.

   
   
 
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi s_ling,

What control/ technology are you using? Data Control?

Are the dbgrid and textboxes are bounded?

In my code, try to use "Exit Sub" before the labels.
0
 

Author Comment

by:s_ling
Comment Utility
hi ryancys ,

I'm using DAO.

the dbgrid is bounded butta the text boxes and combo boxes are manual.

0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
s_ling,

Are you asking for checking for the Enter key?

If so, you could use the KeyPress event of the combo box, and check if ENTER key is pressed. Then check if the entry in Combo box exists, if exists, then display the details or else, go to Add mode.

Like this:

Private Sub Combo1_KeyPress(KeyAscii As Integer)
If KeyAscii = vbKeyReturn Then
    'check if record exist here.
    ':
   
    'if record exist then
        'display data.
    'else
        'go to add mode.
    'end if
   
End If
End Sub

Cheers
0
 

Author Comment

by:s_ling
Comment Utility
hi, valli_an

there is an error when I type in your code.
is it I need to declare the add mode in sub or function?
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi s_ling,

use in Private Sub Combo1_KeyDown() ..
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
s_ling,

its just a model i gave, with comments. You could see a single quote at the start of those lines, like the add mode and all. Ok, you could give a try like this(I have used your code, with some changes. also i have avoided using goto statements):

--------
Private Sub Combo1_KeyPress(KeyAscii As Integer)
   If KeyAscii <> vbKeyReturn Then
      Exit Sub
   End If
   If Combo1.Text = "" Then
    MsgBox "you must key in the data"
    DatPecref.Recordset.Edit
    DatPecref.Recordset.CancelUpdate
    Exit Sub
   End If
   
  With DatPecref.Recordset
      .FindFirst "PECREFNO='" & Combo1 & "'"
      If .NoMatch = True Then
          'Not Found
         DatPecref.Recordset.Update
         cmdOk.Enabled = False
         MsgBox "data saved"
         DatPecref.Refresh
        DBGRID.Enabled = True
      Else
        'Found It
         DatPecref.Recordset.CancelUpdate
         DatPecref.Recordset.Fields("PECREFNO").Value = Combo1
         DatPecref.Recordset.Fields("ACCOUNT").Value = Combo2
         DatPecref.Recordset.Fields("LISENS1").Value = Combo3
         DatPecref.Recordset.Fields("MPLAN1").Value = txtmanagement
         DatPecref.Recordset.Fields("MPLAN2").Value = txtPlan
         DatPecref.Recordset.Fields("ISSUDATE").Value = txtIssue
         SQL = "SELECT INVDATE,BLKNO,BLKAREA,STRIPNO,STRIPLEN,TEAM,REMARK,ATTACH FROM INVFILE WHERE PECREFNO
= '" & Combo1.Text & "'"
        DatInvfile.RecordSource = SQL
        DatInvfile.Refresh
   End If    
End Sub
-------------
0
 

Author Comment

by:s_ling
Comment Utility
hi valli_an ,

there is an error while compile
this code :
"DatPecref.Recordset.CancelUpdate"

run time error '3020'
Update or CancelUpdate without AddNew or Edit


0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
hi, I just paste your code, with some modifications, since you wanted to do something when the user presses ENTER key in Combo box.

Actually what are you trying to do, when the Combo1 Text record is found, and when the record is not found?

Because, in the code you have posted, when not found, it does Update and when found, it does some assigning values to data control.

Cheers.
0
 

Author Comment

by:s_ling
Comment Utility
hi valli_an,

thanx for be so patience.
actually what I was trying to do is ....
after user press the command Add, it will set focus to Combo1 among the Combo boxes and text boxes.

user must key in the PECno in Combo1 and press ENTER. if the data is in the access table "PECREFNO". then, it will display the records that are related including the DBGRID in the other access table.

if not, the user must keep on key in the other data such as AccountNo, Linsensee, Management....and save the data using command button OK.  
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi s_ling,

You need something like this:

Private Sub Combo1_KeyPress(KeyAscii As Integer)
    If KeyAscii <> vbKeyReturn Then Exit Sub
    SQL = "SELECT INVDATE,BLKNO,BLKAREA,STRIPNO,STRIPLEN,TEAM,REMARK,ATTACH FROM INVFILE WHERE PECREFNO  = '" _
            & Replace(Combo1.Text, "'", "''") & "'"
   
    DatPecref.RecordSource = SQL
    DatPecref.Refresh
   
    If DatPecref.Recordset.RecordCount > 0 Then
        'Record Match
        Set DBGrid1.DataSource = DatPecref
        DBGrid1.Refresh
    Else
        'No
       
        'if not, the user must keep on key in the other data such as AccountNo, Linsensee, Management....and
        'save the data using command button OK.
       
        'DatPecref.Recordset.AddNew
        'DatPecref.Recordset.Fields!PECREFNO = Combo1
        '.
        '.
       
        'DatPecref.Recordset.Update
       
    End If
End Sub

'Hope will help.
0
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 50 total points
Comment Utility
Private Sub Combo1_KeyPress(KeyAscii As Integer)
  If KeyAscii <> vbKeyReturn Then
     Exit Sub
  End If
  If Combo1.Text = "" Then
   MsgBox "You must key in the PEC Ref. Number."
   Exit Sub
  End If
 
 With DatPecref.Recordset
     .FindFirst "PECREFNO='" & Replace(Combo1.Text,"'","''") & "'"
     If .NoMatch = False Then
        'Found - Display it.
        cmdOk.Enabled = False
        MsgBox "data displayed"
        DatPecref.Refresh
     Else
       'Not Found - Allow user to enter data.
       CmdOk.Enabled = True
     End If
  End With
        SQL = "SELECT INVDATE,BLKNO,BLKAREA,STRIPNO,STRIPLEN,TEAM,REMARK,ATTACH FROM INVFILE WHERE
PECREFNO
= '" & Combo1.Text & "'"
       DatInvfile.RecordSource = SQL
       DatInvfile.Refresh
End Sub

Private Sub cmdOK_Click()
        DatPecref.Recordset.AddNew
        DatPecref.Recordset.Fields("PECREFNO").Value = Combo1
        DatPecref.Recordset.Fields("ACCOUNT").Value = Combo2
        DatPecref.Recordset.Fields("LISENS1").Value = Combo3
        DatPecref.Recordset.Fields("MPLAN1").Value = txtmanagement
        DatPecref.Recordset.Fields("MPLAN2").Value = txtPlan
        DatPecref.Recordset.Fields("ISSUDATE").Value = txtIssue
        DatPecref.Update
End Sub

-------
Hope this will help you. I have not run the program. So, if you get any error, let me try to help you. I dont think this method will be suitable for you, if you need to give entry option in DBGrid also. Then, your DBGrid entries will be saved and not the PECREF data, when the user does not press OK and quits your program or presses CANCEL.

You need to get to know about Transactions to do this kind of updations, also, you need to use Recordsets or direct updation with Database object, instead of using data controls, as far as I understand. You might need to use something like:

Workspaces(0).BeginTrans

before, the user, starts to enter data to the grid type data. And if the user presses the OK, then you will need to commit the data like,

Workspaces(0).CommitTrans

If the user presses Cancel, then, like this:

Workspaces(0).Rollback

You could also learn about ADO, which is the latest Microsoft technology for database operations, instead of using DAO. But DAO is good for Access.

Cheers.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:s_ling
Comment Utility
Hi valli_an,

there is a run-time error "3426" (action was cancelled by an associated object) in this code
       .FindFirst "PECREFNO='" & Replace                 (Combo1.Text, "'", "''") & "'"

actually what's wrong with this? is it there is something wrong with my other coding?



0
 

Author Comment

by:s_ling
Comment Utility
HI ryancys ,

there is an error in this code:
Set DBGRID.DataSource = DatPecref

the error is "run-time error 430 - class does not support automation or does not support expected interface"
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi s_ling, what grid control you using?

DataGrid or DBGrid? You use Data Control , right?
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi s_ling,

remove the code: Set DBGRID.DataSource = DatPecref

goto DBGrid Control Property and set the DataSource. It will do the same things.
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
You might need to do this before FindFirst like this:


If Not (.BOF Or .EOF) Then
      .MoveFirst
      .FindFirst "PECREFNO='" & Replace                 (Combo1.Text, "'", "''") & "'"
End If

Also, read this article:
http://www.vb-faq.com/Articles/Chang/NoBoundControls.asp
0
 

Author Comment

by:s_ling
Comment Utility
valli_an & ryancys,

thanx for the help !!! butttttt...
I'm still facing the same problems
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
s_ling, could you send your code. Let me have a look at it later, and respond to you.

To My email id: valli_an@hotpop.com
Cheers.
0
 

Author Comment

by:s_ling
Comment Utility
valli_an,

I cannnot send my email to you because your mail box is unknown to the mail-system.
have you type in the right id?
0
 

Author Comment

by:s_ling
Comment Utility
ryancys ,

I cannot set the data source of DBGrid to datpecref because the records that I want to display is in the datinvfile.

the records in datpecref is for the table above the DBGrid.
0
 
LVL 49

Expert Comment

by:Ryan Chong
Comment Utility
Hi s_ling, are you rush to this project? If don't then you can email it to me and i will check this later. Thanks.

(I'm using VB6 and Access 2000)

my email is ryancys78@yahoo.com
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
o, sorry, yes that one was wrong, i have one in hotpop, though. but you could send it to valli_an@softhome.net.

Also, if possible, some details, and your database. :)
0
 

Author Comment

by:s_ling
Comment Utility
ryancys ,

I'm not in rush
actually this is a project for my industrial training in a company...
I'm using VB6 and Access 97.

thanx :-)


0
 

Author Comment

by:s_ling
Comment Utility
thanx!valli_an

I have sent you my project...
0
 
LVL 9

Expert Comment

by:Valliappan AN
Comment Utility
s_ling, to which id did you send the project?
0
 

Author Comment

by:s_ling
Comment Utility
valli_an@softhome.net.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now