Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2016
    Posts
    3

    Unanswered: Looping an update query for a bar code reader

    Hi Folks,

    Apologies if i have posted this incorrectly, this is my first time on this forum!

    I am trying to get a query to loop until the user replies 'no' to a message box. For a bit of context; I have created an inventory database for our field stores, users can book equipment for use and when they come to collect it this query is designed to update the loans table to change the item from being 'booked' to being 'loaned' as they scan each item to issue. I don't want it to loop without user input as the idea is this will ensure they are only going to be loaned the items that they collect and scan and equally I don't want them to have to keep running the query manually.

    Would appreciate a little advice. It may be I am going about this the wrong way entirely, but what i have will work fine provided i can get the loop right. I have inserted the code i think i need and commented it out, i am not sure exactly where it needs to fall. The code is below.


    Private Sub Run_Issue_Booked_Items_Update_Query_Click()

    If IsNull(Me.Show_User_Bookings_subform.Form!Item) Then

    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "No items were found to issue. This may be because they are not due out yet, please check the user bookings."
    Style = vbInformation
    Title = "No Items Found"
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    Cancel = True
    Else
    DoCmd.SetWarnings (WarningsOff)
    DoCmd.OpenQuery "Issue Booked Items Update Query"
    'If MsgBox("Do you want to issue more items?", vbYesNo) = vbYes Then
    'loop
    'else
    DoCmd.SetWarnings (WarningsOn)
    End If
    Me.Refresh
    End Sub

    I have looked at several examples of loops but they are not in the same context so i cannot work out how i can use a loop in here. If at all possible i would like to avoid converting the query to SQL and running it as code but if that is the only or best way i am open to the idea. FYI i am mostly self taught so still not fully aware of the correct terminolgy for some aspects of databases.

    Many thanks in Advance,

    Graham

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ?
    a barcode scanner is usually configured as a keyboard subsitute/extension.
    So think of the barcode reader as a keyboard

    provide embedded codes on a preprinted sheet to pull up the right form. (that could be opening up an instance of Access openign the right application and autoexec and open a data capture form)

    scan the serial number of the device being loaned out plus the employee / user ID
    update your table
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2016
    Posts
    3
    Quote Originally Posted by healdem View Post
    ?
    a barcode scanner is usually configured as a keyboard subsitute/extension.
    So think of the barcode reader as a keyboard

    provide embedded codes on a preprinted sheet to pull up the right form. (that could be opening up an instance of Access openign the right application and autoexec and open a data capture form)

    scan the serial number of the device being loaned out plus the employee / user ID
    update your table
    Sorry, I have obviously caused confusion mentioning the bar code scanner, the issue is with making the query loop until they user responds to a msgbox saying no. I am quite happy with the bar code reader and it's implementation.

    I honestly have no idea what you mean in the second line, sorry.

    The whole aim of this is to avoid having to scan the user and the item over and over. I have set the on-open of the form to ask for the user ID and then place it into a textbox that the query can refer to, so it only needs the item bar code to run each time. Sorry i now realise that was not in the code above so not apparent.

  4. #4
    Join Date
    Sep 2016
    Posts
    3

    Solved

    Never mind, i managed to work it out myself with persistence and trial and error. For those that may be interested the solution code is below.

    Private Sub Run_Issue_Booked_Items_Update_Query_Click()

    If IsNull(Me.Show_User_Bookings_subform.Form!Item) Then
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Msg = "No items were found to issue. This may be because they are not due out yet, please check the user bookings."
    Style = vbInformation
    Title = "No Items Found"
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    Cancel = True
    Else
    Do Until MsgBox("Do you want to issue more items?", vbYesNo) = vbNo
    DoCmd.SetWarnings (WarningsOff)
    DoCmd.OpenQuery "Issue Booked Items Update Query"
    Loop
    End If
    DoCmd.SetWarnings (WarningsOn)
    Me.Refresh
    End Sub

Posting Permissions

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