Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2009
    Posts
    7

    Smile Control Data


    Can you have a controls data which is pulled from another subform control be saved with the current record when advancing to the next record? This is the control source =[Serial Number].column(2)

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Probably but why would you want to do that? Maybe a little more description would be helpful. What fields of the two tables are involved ?

  3. #3
    Join Date
    Mar 2009
    Posts
    7
    RuralGuy,

    I need to have two fields to query in the future to recall the installation record because the serial number and or the asset tag may be missing down the road. I want to speed up the data entry screen by not having the end user type both fields. When the equipment is first entered into inventory these two fields are entered. Then when it is installed any pertinent information about the install will be entered into the install table

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Does your Inventory table have a PrimaryKey field? I would think you would be using a ComboBox to enter the equipment into the Installation table so it could look up existing equipment.

  5. #5
    Join Date
    Mar 2009
    Posts
    7
    Yes, the serial number and smasd tag are combo and they pull the data from the equipment table. See the attachment.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Your design has normalization problems. Is the [Serial No] field in the [Equipment] table the same value as the [Serial Number] field in both the [Install] table and the [Install Details] table?

  7. #7
    Join Date
    Mar 2009
    Posts
    7
    Yes they are the same

  8. #8
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    All you need in the Install table is the [ID] field from the [Equipment] table as a ForeignKey then all of the fields from both tables would be available in a query as if they were in one table. Your [Install Details] table has repeating fields, which makes the structure suspect. There is no reason to have the [Serial Number] in more than one table.

  9. #9
    Join Date
    Mar 2009
    Posts
    7
    I have the tables redesigned with a install form created. The form works great as long as I do not change it to a data entry form. I need to be able to allow the end user to select the serial number from a drop down list. I have tried changing the serialnumber field to a combo box but then if I try to advance to a new record it will not create a record in the install and/or install details tables. How can I create a combo box on the original form to do this?

  10. #10
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Post the SQL for the RowSource of the ComboBox please. What are your values for:
    1) RowSourceType
    2) BoundColumn
    3) ControlSource

  11. #11
    Join Date
    Mar 2009
    Posts
    7
    Sorry for the delay. It was the control source. I have a new issue now. I was using this code on my beforeupdate and it worked great until I changed the tables and referenced the PK instead of the table field that I had configured as a look up in the installsheet table. It fails on the line
    "If DCount("EQPID", "InstallSheet", stLinkCriteria) > 0 Then". I get a runtime error 3464 Data type mismatch in criteria expression. If I mouse over the code everything shows me the correct record id for me.eqpid.value during the debug. Any suggestions? Thank you for your previous help, I'm getting closer.

    Private Sub Combo10_BeforeUpdate(Cancel As Integer)
    '*********************************
    'Code sample courtesy of srfreeman
    '*********************************
    Dim SID As String
    Dim stLinkCriteria As String
    Dim rsc As DAO.Recordset
    Set rsc = Me.RecordsetClone

    SID = Me.EQPID.Value

    SID = Me.Combo10.Value
    stLinkCriteria = "[EQPID]=" & "'" & SID & "'"
    'Check InstallSheet table for duplicate SMASD Tag

    If DCount("EQPID", "InstallSheet", stLinkCriteria) > 0 Then
    'Undo duplicate entry
    'Me.Undo
    'Message box warning of duplication
    MsgBox "Warning Asset Tag " _
    & SID & " has already been entered. " _
    & " Use the edit screen to modify that Install Sheet. " _
    '& vbCr & vbCr & "You will now been taken to the record.", _
    'vbInformation, "Duplicate Information"
    'Go to record of original SMASD Tag Number
    ''rsc.FindFirst stLinkCriteria
    ''Me.Bookmark = rsc.Bookmark
    End If
    Set rsc = Nothing

    End Sub

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    You should have really started a new thread with this issue. It sounds like the [EQPID] field is numeric. Try:
    stLinkCriteria = "[EQPID]=" & SID

  13. #13
    Join Date
    Mar 2009
    Posts
    7
    That worked!! The only thing is it makes reference to the record id and not the actual asset tag and/or serial number depending on which form the end user has open. I have found the bottom section of the code only works if the records are in the record selections while the form is open. If I close the form and try to create a new record using the same id it fails on the me.Bookmark = rsc.Bookmark. Thats why in my version I modified the message and have an edit screen for the user to open and modify that record. If you have any suggestions to fix this it would be greatly appreciated. You've been right on the money with everything thus far. Thank you once again.

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I don't know why you are not using the NotInList event of your ComboBox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 11-25-2013, 10:56 AM
  2. Using tab control pages
    By queenbee in forum Programming
    Replies: 1
    Last Post: 02-28-2009, 09:43 PM
  3. Tab Control Pages
    By queenbee in forum Access
    Replies: 1
    Last Post: 02-28-2009, 10:21 AM
  4. Sending control to specific control
    By wasim_sono in forum Programming
    Replies: 2
    Last Post: 04-19-2007, 08:19 AM
  5. Grid Control - or something like that.
    By rhunt in forum Programming
    Replies: 1
    Last Post: 12-15-2005, 04:46 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums