Can I have input in a form from one db and output into another? 1

[ApparentlyInept]

Ok folks this is what I'm trying to do and its kicking my butt. I'm trying to set up a database to track equipment. I have all my information for the equipment in one database but would like to set up a sort of check in/out form that puts that information into a new database so I can keep historical documentation of who has signed out the information and when. This may not even be possible but if anybody can help me I'd really appreciate it.

 

[ApparentlyInept]

Jedel....this may make me sound stupid but I've never used a delete query....haven't even touched Access in over 4 years. I might need ya to spell this out a little more.

Phil

 

[ApparentlyInept]

I'm trying it but not exactly getting it to work. This is what my coding looks like. I have it as an OnClick action. Its not liking the * I am wanting it to delete anything From TableA(ITAMS) that is in TableB(Equipment_Tracking) that has a date in field 310 Recieved. Then save TableB(Equipment_Tracking) and close the form. What am I doing wrong? Am I trying to do too much at once?


Private Sub Close_Click()
On Error GoTo Err_Close_Click

Dim defin As String
DELETE ITAMS.*, [Equipment_Tracking].[Serial_Number], [Equipment_Tracking].[310_Recieved]
FROM [Equipment_Tracking] INNER JOIN ITAMS ON [Equipment_Tracking].[Serial_Number] = ITAMS.[Serial_Number]
WHERE ((([Equipment_Tracking].[Serial_Number]) = "True") And (([Equipment_Tracking].[310_Recieved]) = "True"))

DoCmd.RunSQL defin

defin = "DELETE * FROM ITAMS (((Where ITAMS.Serial_Number)= Equipment_Tracking.Serial_Number))"
DoCmd.RunSQL defin


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.Close

Exit_Close_Click:
Exit Sub

Err_Close_Click:
MsgBox Err.Description
Resume Exit_Close_Click

End Sub

 

[jedel]

OK.

Firstly, There is no such thing as a stupid question. I was in the same boat with Delete SQLs as you were and I was also tearing my hair out.

I have tried to mimick your db so I can create the Delete query. The code below is as close as I can get it. I have a feeling that you have a few Check boxes and such that you are not telling me about.

After having another read of your last post, I have placed the code in your delivery date "AfterUpdate" Event. This eliminates the need for other buttons and checkboxes.

Place the code below into your delivery date's "AfterUpdate" Event and letme know how you go.

Dim defin As String
If Not IsNull(Me.Sign_Out_Date) Then
DoCmd.SetWarnings False
defin = "DELETE * FROM TableA WHERE (((TableA.[Serial Number])=[Forms]![SignoutForm]![Serial Number]));"
DoCmd.RunSQL defin
DoCmd.SetWarnings True
    If MsgBox("Do you want to leave this form Now?", vbQuestion + vbYesNo) = vbYes Then
    DoCmd.Close
    Else
    End If
Else
End If

Hope this is what you were after.

"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."

 

[ApparentlyInept]

Only check boxes I have are in my "Add Equipment" form, "Update Equipment" form and TableA(ITAMS) and these are to indicate whether the equipment is classified or not. The form we were working on had nothing to do with the check boxes. But I think that what you posted will work, and if not it gave me a good start to muddle through until I figure it out. Thx alot Jedel! I'll let ya know if I have any other questions or just can't seem to get this to work....

Phil

 

[ApparentlyInept]

Jedel-

I posted it into my OnClick intead of after Update because I wanted to control when the form closed and deleted the record. Thing is it didn't work. I changed what you had as Sign_Out_Date to fld310_Recieved because I want that field to control whether or not the record is deleted from ITAMS(FieldA). When I click on the button I get this Error- "Compile Error: Method or data member not found." and it points me to this line:

If Not IsNull(Me[highlight].fld310_Recieved[/highlight]) Then
                          [i]it highlights this portion.[/i]

Did I mess something up when I changed things a little?


here is everything I have on my OnClick event right now.

Private Sub Close_Save_Click()
On Error GoTo Err_Close_Save_Click


    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    
    Dim defin As String
    If Not IsNull(Me.fld310_Recieved) Then
    DoCmd.SetWarnings False
    defin = "DELETE * FROM ITAMS WHERE (((ITAMS.[Serial Number])=[Forms]![310_Receipt]![Serial Number]));"
    DoCmd.RunSQL defin
    DoCmd.SetWarnings True
    If MsgBox("Do you want to leave this form Now?", vbQuestion + vbYesNo) = vbYes Then
    DoCmd.Close
    Else
    End If
Else
End If


Exit_Close_Save_Click:
    Exit Sub

Err_Close_Save_Click:
    MsgBox Err.Description
    Resume Exit_Close_Save_Click
    
End Sub

Sorry if I'm getting to be a pain in your butt.

Phil

 

[ApparentlyInept]

Jedel-

Got it to work after playing around with the coding a bit. Do you happen to know if there is a way to put what serial number is going to be deleted into the message box that comes up when it comes up? Btw Thx ALOT for all the help.

Phil

 

[jedel]

Yup,

Replace the MsgBox line with this:

 If MsgBox ("You are about to delete Serial Number - " & me.[Serial_Number] & " and leave the form. Do you want to continue?", vbQuestion + vbYesNo)= VByes Then

NOTE:
The [Serial_Number] is the name of the field currently open in your form. It will pull the data from that.

Cheers

Dean

"The most overlooked advantage of owning a computer is that if they foul up there's no law against whacking them around a bit."