S
Sue
Hi there
I am slowly losing the will to live here!
Basically what I'm trying to do, is have text entered into a textbox
on an excel-based VBA-driven form, saved as a new record in a pre-
established table in Access (called tblProgramme, which has 3 columns:
an Autonumber, ProgrammeName and ProgrammeType). I then need the
database to refresh so that the new entry appears in another listbox,
allowing them to select it and associate it with the initiative they
are inputing information for.
I would also like the code to check that the text (which will be the
name of a new programme of work) isn't already in the table. In which
case, a msg box will tell the user "this item is already in the
list.." etc etc.
The small form with a textbox and command button on it appears when
the user clicks another command button ("Add New Programme Name") on
another form (Key Programmes).
While this should be simple, I just can't get it right and know I'm
overlooking something? Undoubtedly has something to do with the fact
that I have indicated where I want the record saved???
Here's what I have so far:
In a separate module (public_var) I have:
Sub FindDatabasePath()
path1 = "\\xxxxxxxxx\xxxxxxxx\xxxxxxx\xxx\xxxxxxxxx"
path1 = "" & path1 & "" & "\xxxxxxxx - xxxxxxxxxx Database.mdb"
End Sub
'-----------------------------------------------------------------
Private Sub cmbok_click()
Set ws = DBEngine.Workspaces(0)
Dim rsA As Recordset
Call FindDatabasePath
Set db = ws.OpenDatabase(path1)
Call SaveR
rsA.Update
rsA.Close
db.Close
Set rsA = Nothing
Set db = Nothing
Unload frmAddProgramme
frmStrategy.Show
End Sub
'-----------------------------------------------------------------
Private Sub SaveR()
Dim MyObject As Object
Set MyObject = txOverallProgramme
With frmAddProgramme
..txOverallProgramme = CheckBlank(rsA.Fields(2))
End With
End Sub
'-----------------------------------------------------------------
Function CheckBlank(chkvl As Variant)
If chkvl = "" Then
CheckBlank = Null
Else
CheckBlank = chkvl
End If
End Function
Any help would be truly appreciated!
Thanks in advance,
Sue
I am slowly losing the will to live here!
Basically what I'm trying to do, is have text entered into a textbox
on an excel-based VBA-driven form, saved as a new record in a pre-
established table in Access (called tblProgramme, which has 3 columns:
an Autonumber, ProgrammeName and ProgrammeType). I then need the
database to refresh so that the new entry appears in another listbox,
allowing them to select it and associate it with the initiative they
are inputing information for.
I would also like the code to check that the text (which will be the
name of a new programme of work) isn't already in the table. In which
case, a msg box will tell the user "this item is already in the
list.." etc etc.
The small form with a textbox and command button on it appears when
the user clicks another command button ("Add New Programme Name") on
another form (Key Programmes).
While this should be simple, I just can't get it right and know I'm
overlooking something? Undoubtedly has something to do with the fact
that I have indicated where I want the record saved???
Here's what I have so far:
In a separate module (public_var) I have:
Sub FindDatabasePath()
path1 = "\\xxxxxxxxx\xxxxxxxx\xxxxxxx\xxx\xxxxxxxxx"
path1 = "" & path1 & "" & "\xxxxxxxx - xxxxxxxxxx Database.mdb"
End Sub
'-----------------------------------------------------------------
Private Sub cmbok_click()
Set ws = DBEngine.Workspaces(0)
Dim rsA As Recordset
Call FindDatabasePath
Set db = ws.OpenDatabase(path1)
Call SaveR
rsA.Update
rsA.Close
db.Close
Set rsA = Nothing
Set db = Nothing
Unload frmAddProgramme
frmStrategy.Show
End Sub
'-----------------------------------------------------------------
Private Sub SaveR()
Dim MyObject As Object
Set MyObject = txOverallProgramme
With frmAddProgramme
..txOverallProgramme = CheckBlank(rsA.Fields(2))
End With
End Sub
'-----------------------------------------------------------------
Function CheckBlank(chkvl As Variant)
If chkvl = "" Then
CheckBlank = Null
Else
CheckBlank = chkvl
End If
End Function
Any help would be truly appreciated!
Thanks in advance,
Sue