+ Reply to Thread
Results 1 to 8 of 8

Check if the value in textbox is present in Access table using vba

  1. #1
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Check if the value in textbox is present in Access table using vba

    Hi All

    There is a userform where you have to enter the batchno in the textbox . and then VBA code will check the Access database for that batchno.
    if found then check tht batchno in Sheet1 , column A . if its present then display cases,pages and policy no to the corresponding batch number found.

    I have written the code but it doesn't work. I hope anyoone make the changes accordingly.

    Please Login or Register  to view this content.
    Thanks

  2. #2
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    2016 Win10
    Posts
    7,149

    Re: Check if the value in textbox is present in Access table using vba

    People won't help you if you don't answer the questions they ask when offering help.

    I'll give it another shot though - what datatype is "BatchNo"?
    What "Doesn't Work"? Do you get an erro message? Does nothing happen? etc ...

  3. #3
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Check if the value in textbox is present in Access table using vba

    Can you pls tell us which is the difference between this thread and this one?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  4. #4
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Check if the value in textbox is present in Access table using vba

    Thanks Kyle . I made it working but now another thing is if I have to check all the batchnos in Access table <=textbox1.value and then check in column A of sheet1 so if present then add the corresponding information in sheet1 of those batchnos that haven't been done earlier. So to find out if they have been done or now I am using ws.cells(1,"C").value that means "Cases" field if its empty of a particular batchno then add the corresponding data like cases,pages,policyno of that record otherwise move to next row.
    in the following code , the loop doesn't stop.
    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Check if the value in textbox is present in Access table using vba

    Fotis, I want to close the other thread but i am not able to. Can you pls do that for me if possible?

  6. #6
    Forum Contributor
    Join Date
    02-20-2009
    Location
    bristol
    MS-Off Ver
    Excel 2003
    Posts
    880

    Re: Check if the value in textbox is present in Access table using vba

    Kyle, I forgot to write rs.movenext statement but even now the loops keep on going and doesn't stop.
    Please Login or Register  to view this content.

  7. #7
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Check if the value in textbox is present in Access table using vba

    i closed your other thread. So you can continue on this one even that i can not understand how A member with 749 posts until Today does not knows the forum rules very well.

  8. #8
    Forum Expert Arkadi's Avatar
    Join Date
    02-13-2014
    Location
    Smiths Falls, Ontario, Canada
    MS-Off Ver
    Office 2016
    Posts
    4,895

    Re: Check if the value in textbox is present in Access table using vba

    aman,

    If you are only looking for a match, why are you making your recordset <= TextBox1.Value instead of just = TextBox1.Value ?
    What is lastrow as a typical value? maybe it is just looping very long in the "for i" loop?
    Also, what is the recordcount for rs? maybe you have a LOT of records coming down?
    Are you calling this macro directly, or does another macro call it? any chance the infinite loop is not caused by this macro? Also, I'd change
    Please Login or Register  to view this content.
    . I'm no expert, but I do know that depending on the cursor and lock type you may get odd results when trying to use .eof or .bof, i've had macros "freeze" when using them unless I make sure I use lock/cursor that supports it. You may want to try:
    Please Login or Register  to view this content.
    and see if that helps at all.
    The code is pretty painful to read/follow without some indendation... and a workbook would help with context and seeing what is wrong.
    Please help by:

    Marking threads as closed once your issue is resolved. How? The Thread Tools at the top
    Any reputation (*) points appreciated. Not just by me, but by all those helping, so if you found someone's input useful, please take a second to click the * at the bottom left to let them know

    There are 10 kinds of people in this world... those who understand binary, and those who don't.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Check if column D value is present in Access table Ref Field VBA
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-15-2014, 06:01 AM
  2. Replies: 2
    Last Post: 09-03-2014, 03:37 AM
  3. Finiding max value in Access table and populate in Excel userform textbox
    By LisaPatch in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2011, 05:44 AM
  4. Replies: 3
    Last Post: 08-24-2005, 04:05 PM
  5. How can I use VBA ADO to check if a table exists in an Access DB?
    By Ai_Jun_Zhang in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 08-24-2005, 02:52 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1