Trying to make different text appear each time form is opened....

[drblackard]

I am building a database for our church to collect information for our membership. All is going fine and the database works great. Now I am trying to get a little fancy with it. I would like a scripture to appear on the main contact form when the form is opened up. I would also like to make a table with the quotes/scriptures for the form to draw from in case i would like to give my own quotes. The text should appear randomly and should change everytime the form is opened. I know there is a way to do this, but can someone help me with this. I have searched the database but have not found a reference. Thanks.

 

[Remou]

Say you have a table, tblQuotations, with a numeric ID and a Quotation field, containing 100 quotes. The ID runs sequentially from 1 to 100. You could then create a text box on your form with a Control Source:
[tt]=DLookUp("Quotation","tblQuotations","ID=" & Int((100*Rnd())+1))[/tt]

 

[drblackard]

I appreciate the quick response but I can't seem to get this to work. I created a table called tblQuotations. I then created one field that was named ID and one field that was named Quotation. I then gave the ID the number 1 and typed in 1 John 5:1 scripture. I then created a text box on the form and type in the above control source. Nothing happened. I received #error in the box. Did I do something wrong.

 

[Remou]

This bit:
Int((100*Rnd())+1))
Says select a random integer up to 100. If you have only one quotation, you need to change 100 to 1, that is, select a random interger between 1 and 1

 

[drblackard]

Thanks. You are Good. That solved the problem. Now, I'm trying to build a backup feature. Any suggestions.

 

[Remou]

Glad I could help.
You may like to look at this thread:
Backup of open database - how do I do this from code.
thread705-78422
If it does not suit, a new thread will throw the topic open to debate and allow you to receive a full range of answers on what may prove a controversial subject.

 

[MajP]

I beefed up Remou's idea a little. The problem I see is when you do not find an ID that matches, you are going to throw an error. Also you have to change the code to meet the amount of versus you have.
This will work no matter how many ID's you add, and will always find a verse.

Public Function fncRandomVerse() As Variant
  Dim maxID As Integer
  Dim minID As Integer
  Dim range As Integer
  Dim selectedID As Integer
  maxID = DMax("ID", "tblQuotations")
  minID = DMin("ID", "tblQuotations")
  range = maxID - minID
  Do
    selectedID = minID + CInt(range * Rnd())
     fncRandomVerse = Nz(DLookup("quotation", "tblQuotation", "ID = " & selectedID), "")
  Loop Until Not fncRandomVerse = ""
  
End Function

then in the control source

=fncRandomVerse

 

[MajP]

This is probably more bullet proof.

Public Function fncRandomVerse2() As String
  Dim rs As DAO.Recordset
  Set rs = CurrentDb.OpenRecordset("tblQuotations")
  Dim intRecordCount As Integer
  intRecordCount = rs.RecordCount
  rs.Move (CInt(intRecordCount * Rnd()))
  fncRandomVerse2 = rs.Fields("quotation")
  rs.close
End Function