Solved

Append to a Litbox

Posted on 2007-11-18
4
488 Views
Last Modified: 2013-11-27
Hi,

I have two forms: frmScanTags and frmScanTagPop

frmScanTags has a listbox lbScannedtags.  frmScanTagPop acts like a popup for which is opened above the frmScanTags table and allows the user to enter a tag number in the single field on this form.

What i am trying to do is what ever tag is entered into the frmScanTagPop form is checked to see if it excits in tblAssignedTags and if so is apended to the listbox in the frmScanTags form.

It would be like querying tblAssignedTags with the data which is entered in the popup form and adding the results to the listbox

Hope someone can help me find a way to achieve this.

Anthony
0
Comment
Question by:d10u4v
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 20310701
Hi Anthony,
it sounds like you are almost there.
If your popup form is correctly adding tags all you need to do is requery the listbox on the form that invoked the popup.

So in your code that invokes the popup, after the openForm command.
    lbScannedtags.requery


hth

Alan



0
 

Author Comment

by:d10u4v
ID: 20311079
That's the problem, its not adding tags, that is what i'm trying to achieve. :(
Anthony
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 20311297
Hi Anthony,

Not really sure how your are invoking your popup form (frmScanTagPop)
but I would do it like this:

I would append and Item to listbox --Add tag--
You can do this by modifying the recordsource for the listbox
eg:
SELECT tblAssignedTags.ID, tblAssignedTags.tag FROM tblAssignedTags; Union All Select 0, "--Add tag--"  FROM tblAssignedTags Where ID = 1

Then you can add a AfterUpdate Event procedure to the the listbox, to invoke your popup form (frmScanTagPop)
like so:

With me so far?

Alan


Private Sub lbScannedtags_AfterUpdate()
 If lbScannedtags.Column(0) = 0 Then
   'Open your popup form
   DoCmd.OpenForm "frmScanTagPop", acNormal, , , acFormAdd, acDialog
 End If
 
 lbScannedtags.Requery
 
End Sub

Open in new window

0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
ID: 20311476
Hi Anthony,
sorry stopped for dinner, lol.

Anyway after you get the first part working you need to add some code to the popup forms before update event, to check if the the new tag they are trying to enter already exists and if it does, cancel the update.
Like so:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
    ' Make sure the tag field is not null or empty string
    If Nz(Me.txtTag, 0) <> 0 And Me.txtTag <> "" Then
        ' Build the filter criteria
        Dim strWhere As String
        strWhere = "Tag='" & Me.txtTag & "'"
         
         ' Count the recods in the table matching the criteria
         If DCount("ID", "tblAssignedTags", strWhere) Then
           MsgBox ("Already have tag for " & txtTag)
           Cancel = True
           DoCmd.Close acForm, Me.Name, acSaveNo
         End If
        
    End If
 
End Sub

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

627 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