Control Data

Griz1980 · #1 03-28-2009, 02:01 PM

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)

RuralGuy · #2 03-28-2009, 05:17 PM

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 ?

Griz1980 · #3 03-29-2009, 08:25 AM

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

RuralGuy · #4 03-29-2009, 08:33 AM

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.

Griz1980 · #5 03-29-2009, 10:58 AM

Yes, the serial number and smasd tag are combo and they pull the data from the equipment table. See the attachment.

RuralGuy · #6 03-29-2009, 01:47 PM

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?

Griz1980 · #7 03-30-2009, 04:54 AM

Yes they are the same

RuralGuy · #8 03-30-2009, 05:11 AM

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.

Griz1980 · #9 03-30-2009, 09:25 AM

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?

RuralGuy · #10 03-30-2009, 09:49 AM

Post the SQL for the RowSource of the ComboBox please. What are your values for:
1) RowSourceType
2) BoundColumn
3) ControlSource

Griz1980 · #11 04-01-2009, 02:51 PM

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

RuralGuy · #12 04-01-2009, 03:00 PM

You should have really started a new thread with this issue. It sounds like the [EQPID] field is numeric. Try:
stLinkCriteria = "[EQPID]=" & SID

Griz1980 · #13 04-01-2009, 03:13 PM

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.

RuralGuy · #14 04-01-2009, 03:22 PM

I don't know why you are not using the NotInList event of your ComboBox.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Using tab control pages	queenbee	Programming	1	02-28-2009 06:43 PM
Tab Control Pages	queenbee	Access	1	02-28-2009 07:21 AM
Sending control to specific control	wasim_sono	Programming	2	04-19-2007 06:19 AM
Grid Control - or something like that.	rhunt	Programming	1	12-15-2005 01:46 PM
Filter data on a form from another control on the same form	WonkeyDonkey	Forms	0	11-12-2005 01:09 AM