Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273

    Unanswered: Create Add New record through Command Button

    Hi, Please help...

    I am trying to create a command button that would add a new record, I have an input box to put in a Person ID and then once I click OK I want it to populate the textbox on the form and then populate a new record with an Autonumber. This below is my code I am trying to get to work, but all that happens is that the Input box appears, once I input the ID and click OK it sometimes writes to the tblData and nothing else or nothing at all, how can I get around this issue? Thanks, Jez

    Private Sub btnCreateNewInput_Click()
    Dim rs As Object
    varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
    If varInput = "" Then
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblData WHERE False")
    rs.AddNew
    varNewID = rs.Fields![FormNumber]
    rs.Fields![RPSGBRegNo] = varInput
    rs.Update
    rs.Close
    Set rs = Nothing
    End If
    DoCmd.SetWarnings False
    Me.RecordSource = "SELECT tblData.* FROM tblData WHERE " & _
    "tblData.FormNumber = " & varNewID & ";"
    Me.txtDummy.SetFocus
    End Sub

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Jez

    There are a number of thing that could say about your code, this but try this for a start

    Code:
    Dim rs As Object
    
    varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
    
    If varInput = "" Then Exit Sub ' NO DATA ENTERED OR CANCELLED
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblData WHERE False")
    rs.AddNew
    varNewID = rs.Fields![FormNumber]
    rs.Fields![RPSGBRegNo] = varInput
    rs.Update
    rs.Close
    Set rs = Nothing
    
    DoCmd.SetWarnings False
    Me.RecordSource = "SELECT tblData.* FROM tblData WHERE " & _
    "tblData.FormNumber = " & varNewID & ";"
    Me.txtDummy.SetFocus
    DoCmd.SetWarnings True
    and do some data validation in the varInput, or put an input box on the form and check that.

    I assume FormNumber is numeric and not the autonumber field !


    MTB

  3. #3
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    Thanks for the reply MTB,

    I dont mind the criticism of my code as I'm a beginer at building Database's and learning as I go along, what was wrong with my code?

    The FormNumber is autonumber and not numeric, does this affect it? as when I have tried the code, the textfield for the autonumber doesnt populate. but when I look in the actual table of data its there.

    Is there anything I should be doing differently?

    Jez

  4. #4
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Again

    I did get my wire a little crossed, but did you notice the change I had made.

    Anyway, in the light of your last post I suggest this

    Code:
    Dim rs As DAO.RecordSet 'FULLY QUALIFY RECORSET (REFERENCE TO DAO MY BE NEEDED)
    Dim NewID as Long  'ID VALIABLE AS LONG DATA TYPE
    
    varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
    
    
    If varInput = "" Then Exit Sub   ' NO DATA ENTERED OR WAS CANCELLED
    
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblData WHERE False")
    rs.AddNew
    NewID = rs.("FormNumber")
    rs.("RPSGBRegNo") = varInput
    rs.Update
    rs.Close
    Set rs = Nothing
    
    DoCmd.SetWarnings False
    Me.RecordSource = "SELECT tblData.* FROM tblData WHERE " & _
    "tblData.FormNumber = " & NewID & ";"
    Me.txtDummy.SetFocus
    
    DoCmd.SetWarnings True
    
    me.Requery  ' RESET FORM WITH NEW DATA
    I assume FormNumber IS NUMERIC (not Text ?) as this is AutoNumber field ???

    Try that.


    MTB

  5. #5
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    MTB, I noticed your changes from what I had done, reading through it I began to understand what you had done.

    FormNumber is an autonumber field but yes only numeric.

    The last post you wrote, I have tried and again it still doesnt show the FormNumber once I have put the ID number into the Input box.

    Private Sub btnCreateNewInput_Click()
    Dim rs As DAO.Recordset 'FULLY QUALIFY RECORSET (REFERENCE TO DAO MY BE NEEDED)
    Dim NewID As Long 'ID VALIABLE AS LONG DATA TYPE
    varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
    If varInput = "" Then Exit Sub ' NO DATA ENTERED OR WAS CANCELLED
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblData WHERE False")
    rs.AddNew
    NewID = rs.Fields![FormNumber]
    rs.Fields![RPSGBRegNo] = varInput
    rs.Update
    rs.Close
    Set rs = Nothing

    DoCmd.SetWarnings False
    Me.RecordSource = "SELECT tblData.* FROM tblData WHERE " & _
    "tblData.FormNumber = " & NewID & ";"
    Me.txtDummy.SetFocus

    DoCmd.SetWarnings True

    Me.Requery ' RESET FORM WITH NEW DATA
    End Sub

    Thanks, Jez

  6. #6
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    To try and find out what is going on try adding some tempory lined line this

    Code:
    Private Sub btnCreateNewInput_Click()
    Dim rs As DAO.Recordset 'FULLY QUALIFY RECORSET (REFERENCE TO DAO MY BE NEEDED)
    Dim NewID As Long 'ID VALIABLE AS LONG DATA TYPE
    varInput = InputBox("Enter the RPSGB Reg No", "Add new Data")
    
    If varInput = "" Then Exit Sub ' NO DATA ENTERED OR WAS CANCELLED
    
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblData WHERE False")
    rs.AddNew
    NewID = rs.Fields![FormNumber]
    rs.Fields![RPSGBRegNo] = varInput
    rs.Update
    rs.Close
    
    '###########
    dim SQL as string
    sql="SELECT tblData.* FROM tblData WHERE tblData.FormNumber = " & NewID
    msgbox SQL
    Set rs = CurrentDb.OpenRecordset(SQL)
    msgbox  rs.Fields![FormNumber]  & " : " &  rs.Fields![RPSGBRegNo]
    rs.Close
    '#########
    
    Set rs = Nothing
    
    'DoCmd.SetWarnings False NOT NECESSARY !
    
    Me.RecordSource = SQL  'NOTE SQL VARIABLE USED HERE ALSO
    Me.txtDummy.SetFocus
    
    'DoCmd.SetWarnings True NOT NECESSARY !
    
    Me.Requery ' RESET FORM WITH NEW DATA
    End Sub
    Try that and see what data you get in messages

    When you say 'doesnt show the FormNumber' do you mean doesn't show RPSGBRegNo ? (as FormNumber is AutNumber it is meaningless to the user, only the database is interested).


    MTB

  7. #7
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    MTB, Thanks for that otherway of looking at the code, from doing it this way I now understand what its doing.

    My statement about the FormNumber not showing is yes that doesnt show but the RPSGBRegNo does once its been input from the input box. From your previous thread saying it doesnt need to show as its just for the DB is ok, I have put a search button on there and that does show once the record has been found, thats main thing.

    I will be looking at the Delete record next so I may be back...

    Jez

  8. #8
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    MTB, quick one...

    Back on the create new record thing. If I was to have this on another form but this didnt have an autonumber within the table... Its just the RPSGBRegNo, Name and a check box for if they have a certificate or not. Would I be going down the same line as I was for the previous threads or does it need to be totally different?

    Jez

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Jez

    A question that may give you a sort of answer.

    Does this new table have a primary key Field(s) ?

    If so, then this/these should be required field(s) and will need setting in your new record.

    This will enable you to create a WHERE clause in your Me.Recordsource assignment string to filter on the new record.

    If you do not have a PK field(s) in your table you will not be able to guarantee selecting the record you have just created.

    Does that make sense, if not let me know.


    MTB

  10. #10
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    MTB, I am not sure I have fully understood your thread... the RPSGBRegNo is my Primary Key field. This is based on my original code, maybe I have gone wayward with it.

    Private Sub btnCreateNewInput_Click()
    Dim rs As DAO.Recordset
    varInput = InputBox("Enter the RPSGB Reg No", "Add new Pharmacist")
    If varInput = "" Then Exit Sub
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblPharmacistDetails WHERE False")
    rs.AddNew
    varInput = rs.Fields![RPSGBRegNo]
    rs.Update
    rs.Close
    Dim sQRY As String
    sQRY = "SELECT tblPharmacistDetails.* FROM tblPharmacistDetails WHERE tblPharmacistDetails.RPSGBRegNo = " & varInput
    Set rs = CurrentDb.OpenRecordset(sQRY)
    rs.Close
    Set rs = Nothing
    Me.RecordSource = sQRY
    Me.txtDummy.SetFocus
    Me.Requery
    End Sub

    Jez

  11. #11
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    May be it was me, I assumed that the autonumber (FormNumber) was the PK, but if RPSGBRegNo is the PK then fine, it should work on any form based on a table where the user can enter the PK as in you last post.

    Having said that, shouldn't this

    varInput = rs.Fields![RPSGBRegNo]

    be this

    rs.Fields![RPSGBRegNo]=varInput

    ??

    and

    Set rs = CurrentDb.OpenRecordset(sQRY)
    rs.Close

    is redundant ?


    MTB

  12. #12
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    This second create new record is to go onto another form, which is looking at a different lot of data. This would be for a Employee table, and on this the RPSGBRegNo is the PK

    Jez

  13. #13
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    It is unusual to have the same primary key in two tables.

    If 'RPSGBRegNo' is the PK for the Emplyee Tabel (ie the persons Reg No) then it would normaly be a Foreign Key in the tblData (relating data to the person). Then tblData would need a different PK

    If 'RPSGBRegNo' is truely a PK in both tables then this would suggest the two table could be combined on the one PK (unless there is a compelling reason not to)??

    Also, if 'RPSGBRegNo' is a PK in the employee table AND a FK in tblData I would suggest you use a ComboBox to select 'RPSGBRegNo' for entry in to the tblData table. tis eliminates data entry error, maintains referential integritiy (even if it is not formaly defined) and has te advantage of autocomplete.

    Sorry for rambling on but there does seem to be some anomaly here, or I can't see the wood for the trees.


    MTB

  14. #14
    Join Date
    Apr 2007
    Location
    Stalybridge, Manchester
    Posts
    273
    MTB, I think we are both getting crossed wires...

    The tblEmployees is a new table with a new PK (the person ID - RPSGBRegNo) the tblData has the PK of the FormNumber.

    All I want to try and do is use a cmd button to create new records for when i input new employees into the tblEmployees. This wont have anything to do with the other form.

    I getting totally confused with the Foreign Key and linking to tables. Sorry I am new to all this.

    Jez

  15. #15
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Well that’s cleared that up.

    tblEmployees ; PK= RPSGBRegNo (user specified/input – as previous post)

    tblData; PK = FormNumber (autonumber?), FK = RPSGBRegNo

    I think a bound form in Data Entry mode is the simple/easiest way to add a new record to both tables.

    For tblEmployee the user type in RPSGBRegNo
    and for tblData user selects required RPSGBRegNo form a ComboBox based on tblEmplyees.


    ???


    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •