Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    Hi,
    Attached is an Access Database that has some VB behind. I have never delved into the world of VB and am unsure as to why its not running correctly. The VB is behind the main form when you enter Defendant details/ Witness Details...you then click 'Add' control to add media to the case. This comes up as an error displaying the word 'No' as written in the code.
    What I want to be able to do is fix this so that the VB will add the entries relating to the amount entered (This all makes sense once the form is entered).
    Just on a side note, in the attached database is also a calendar form with VB behind it that I can apparently link to textboxes by using the code..............needless to say I'm somewhat lost on this...............
    Any Guru's fancy giving me a helping hand?????
    Thanks,
    Nigel

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    The code tests if frmInterviews is loaded. But in your situation, it is a subform of frmDefendant; subforms are not "open" or "loaded" the way they are when you open them directly. So you must modify the code in cmdOK_Click in frmNewMedia to take this into account. The code has to be adapted in several places, and you have to take care to distinguish between the name of the subform as an object in the database window (frmInterviews) and as a control on the main form (frmInterviewsSub).

    Since the code is rather long, I have attached it in a text file.
    Attached Files Attached Files

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    I didn't see a calendar form. What is its name?

  4. #4
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    Sorry,
    I forgot I had to delete the calendar form to get the database below 100k, attached is the calendar by itself.
    Is the code you attached something that would work, or would I need to amend the current coding behind the form? Sorry but I really haven't got a clue with regards to VB............
    Thanks,
    Nigel
    Attached Files Attached Files

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    You should replace the cmdOK_Click procedure (sub) in the code behind frmNewMedia by the code I posted. You can copy it from the text file, delete the existing cmdOK_Click code and then paste the modified version into the code module.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    Hi,
    Tried that, it comes up with the error:
    'Microsoft Access can't find the field 'FrmInterviewSub' referred to in your expression................................help

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    When the user clicks OK, or double clicks a date in the calendar form, the form hides itself. The selected date is still available to the programmer through code, since it has been set in the text box txtDate. You can refer to it as

    Forms!frmCalendar!txtDate

    I fear that his won't make it much clearer for you. It would be a good idea to try to become more familiar with Access VBA before tackling this.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    OK, here is the database with the modified code. But please read my other reply.
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    Hi,
    The database now works thanks to your VBA. The only problem that has arisen now is that I cannot create multiple defendants in relation to the defendant, I presume that is because that in one of the media tables this will create a duplicate entry and a duplicate reference for each media copy.
    What would be the best work around for this?
    Sorry to keep asking questions but I have become truly stuck on this database.
    I also think I get the idea of how to enter the calendar onto the database within text fields. Presumably I go to >On Dbl Click> Expression Builder> All Forms> Frm Calendar>Txt Date, do that in the expression details it would resemble =[Forms]![FrmCalendar]![TxtDate] or similar, so presumably when the user double clicks this takes them to the calendar...............i might be wrong.................
    Thanks for any further help, if you have the time,
    Nigel

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    Sorry, I don't understand the question about defendants.

    About the calendar, you shouldn't use =[Forms]![frmCalendar]![TxtDate] as Control Source, since frmCalendar won't be open all the time. You would need some VBA code to handle this, so I repeat my suggestion that you become more familiar with Access VBA before continuing.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    <blockquote><hr>I cannot create multiple defendants in relation to the defendant<hr></blockquote> Sorry, but that doesn't really make sense. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> You have a single tblDefendants table. Each defendant is a separate record. Are you asking about how to have multiple defendants in a particular action? To do that you would need another table to assign some kind of CaseID to each defendant record. That would allow you to join multiple defendants to a particular case. That hasn't anything to do with VBA, it is an issue of table and relationship design.
    Charlotte

  12. #12
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    Hi,
    Sorry my mistake, I meant mutiple witnesses per defendant............
    Thanks,
    Nigel

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    You have a relationship between tblDefendant and tblInterviews on ID vs intID. Both are AutoNumber fields; this doesn't make sense. Because of this relationship, the frmInterviewsSub subform is related to the main form frmDefendant on ID vs intID.

    You need a separate field DefendantID in tblInterviews of type Number (Long Integer), not AutoNumber. The relationship should be on ID vs DefendantID, and these fields should be used as Link Master Fields and Link Child Fields for the subform.

  14. #14
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Norfolk, England
    Posts
    744
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    Hi,
    So as I understand it I need a relationship between the ID in tbldefendant to DefID in TblInterview. As the DefID will be number, long integer, presumably the user must have to enter some form of reference number (or is this not the case?)?
    Then the next relationship will need to be between IntID & MedIntID and so on.
    If this is the case, I have done it this way and fiddled with the relationships, when I now try to add videos to another new witness entry it displays the 'You cannot add or change a record because a related record is required in 'Tbldefendant''.
    Sorry for so many questions, feel free to tell me to go away.............
    Regards,
    Nigel
    Attached Files Attached Files

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access Database with VBA - Help!!!!!!!!!! (Access 2000)

    You have modified the tables and relationships a bit too far. You have correctly added DefID to tblInterviews. but you shouldn't have changed intID in this table from AutoNumber to Number (Long Integer). Because it is not an AutoNumber any more, it is not filled in automatically.

    For the frmInterviewsSub subform of frmDefendant, set the Link Child Fields property to DefID - see screenshot below. If you do this, the DefID field will be filled in automatically if you create a new record in the subform.
    Attached Images Attached Images
    • File Type: png x.PNG (9.8 KB, 0 views)

Page 1 of 2 12 LastLast

Posting Permissions

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