How it Work function Rnd()?

[KrIs86BsBG]

1. Can you post your db? Zip it first.
2. My guess is you actually need no relationship because you will want to do a cartesian join to get all possibilities and then sort all possibilities.
3. You need an unique field so I would add an autonumber field to each table
ZodiacID, LoveID, FinanceID, WorkID
4. You can do this in a query.

However, I think there may be some complications if you want no repeats in the fields.
Getting a random choice of Love, Finance, Work is easy. Ensuring no replication of the choices until each choice is used up, is a little harder.

I Share the Database I've Made. Please, if you succeed or some other specialist, help me with the various texts with the function I will be grateful to him and will have learned something useful! Again, I say: With the 30 text entries in each category, after request, subtract the texts in random order. I have also made a table of Years, Months, and a Day that should be like a template, and after choosing the year, the month and the day, it will take out information about all the signs of the day, with each sign containing mixed texts from the three categories : Love, Work and Finance.That's what I need! Thanks in advance!

 

[MajP]

and after 9 days the descriptions of the three parameters pass to another sign

Can you explain this part a little more? If I read this correctly I would create 9 days of random (love, work, finance) messages for each zodiac sign. After the first 9 days, the messages for Aries from the first 9 days would get assigned for the next 9 days to Taurus and Taurus's messages to Gemini.... Pisces get shifted to Aries.


That seems a little complicated. Can you simply pull a random message for every day for every zodiac sign?

 

[MajP]

See if this suffices.

Basically if you move to a date it either shows the existing records or creates a new record.
1. I created tblHoroscope.
For every day selected you create a record for each zodiac and pull a random finance, love, and workID.
2. I created queries for each love, finance, work sorted by my random function.

Code in the Form

Private Sub cmdDown_Click()
  Me.txtDate = txtDate - 1
  NewDate
End Sub

Private Sub cmdUp_Click()
  Me.txtDate = txtDate + 1
  NewDate
End Sub
Private Sub txtDate_AfterUpdate()
  NewDate
End Sub

Private Sub Form_Load()
   If Not HasDate(Date) Then
      CreateHoroscope (Me.txtDate)
   End If
   Me.subFrmHoroscope.Requery
End Sub

Public Sub NewDate()
  If IsDate(Me.txtDate) Then
    If Not HasDate(Me.txtDate) Then
      CreateHoroscope (Me.txtDate)
    End If
  End If
  Me.subFrmHoroscope.Requery
End Sub

Code to do the insert

Public Function MajP_Rnd(ID As Long) As Double
  Randomize
  MajP_Rnd = Rnd(Now)
End Function

Public Sub CreateHoroscope(theDate As Date)
  Dim strSql As String
  Dim rs As DAO.Recordset
  Dim LoveID As Long
  Dim WorkID As Long
  Dim ZodiacID As Long
  Dim FinanceID As Long
  Dim HoroscopeDate As String
  
  HoroscopeDate = "#" & Format(theDate, "mm/dd/yyyy") & "#"
  Set rs = CurrentDb.OpenRecordset("Select ZodiacID from Zodiac")
  Do While Not rs.EOF
    'Pulls the first record and is random
    LoveID = DLookup("loveID", "qryRandomLove")
    WorkID = DLookup("WorkID", "qryRandomWork")
    FinanceID = DLookup("financeID", "qryRandomFinance")
    ZodiacID = rs!ZodiacID
    strSql = "Insert Into tblHoroscope (HoroscopeDate, ZodiacID_FK, LoveID_FK, WorkID_FK, FinanceID_FK) "
    strSql = strSql & "values (" & HoroscopeDate & ", " & ZodiacID & ", " & LoveID & ", " & WorkID & ", " & FinanceID & ")"
    'Debug.Print strSql
    CurrentDb.Execute strSql
    rs.MoveNext
  Loop
End Sub
Public Sub CreateHoroscope2(theDate As Date)
  'Creates 30 days out of horoscope without repitition
  
  Dim strSql As String
  Dim rszodiac As DAO.Recordset
  Dim rsLove As DAO.Recordset
  Dim rsWork As DAO.Recordset
  Dim rsFinance As DAO.Recordset
  Dim i As Integer
  Dim LoveID As Long
  Dim WorkID As Long
  Dim ZodiacID As Long
  Dim FinanceID As Long
  Dim HoroscopeDate As String
  Dim NewDate As Date
  NewDate = theDate
  
  Set rszodiac = CurrentDb.OpenRecordset("Select ZodiacID from Zodiac")
  Do While Not rszodiac.EOF ' do each zodiac
     Set rsWork = CurrentDb.OpenRecordset("qryRandomWOrk")
     Set rsLove = CurrentDb.OpenRecordset("qryRandomLove")
     Set rsFinance = CurrentDb.OpenRecordset("qryRandomFinance")
     ZodiacID = rszodiac!ZodiacID
    For i = 1 To 30 ' each available message
        HoroscopeDate = "#" & Format(NewDate, "mm/dd/yyyy") & "#"
        LoveID = rsLove!LoveID
        WorkID = rsWork!WorkID
        FinanceID = rsFinance!FinanceID
        strSql = "Insert Into tblHoroscope (HoroscopeDate, ZodiacID_FK, LoveID_FK, WorkID_FK, FinanceID_FK) "
        strSql = strSql & "values (" & HoroscopeDate & ", " & ZodiacID & ", " & LoveID & ", " & WorkID & ", " & FinanceID & ")"
    'Debug.Print strSql
        rsWork.MoveNext
        rsLove.MoveNext
        rsFinance.MoveNext
        CurrentDb.Execute strSql
        NewDate = NewDate + 1
    Next i
    NewDate = theDate
    rszodiac.MoveNext
  Loop
End Sub

Public Function HasDate(theDate As Date) As Boolean
  HasDate = (DCount("*", "tblHoroscope", "HoroscopeDate = #" & Format(theDate, "MM/DD/YYYY") & "#") > 0)
End Function

Currently do not use CreateHoroscope2, but you can choose to call this instead.
CreateHoroscope does not ensure you limit repetition. It simply pulls a random record every day. CreateHoroscope2 Does 30 days worth of sets of random messages without repetition for each zodiac. This is more complicated and harder to explain. It also takes some time to run because it creates 30 X 12 records.

 

[KrIs86BsBG]

Can you explain this part a little more? If I read this correctly I would create 9 days of random (love, work, finance) messages for each zodiac sign. After the first 9 days, the messages for Aries from the first 9 days would get assigned for the next 9 days to Taurus and Taurus's messages to Gemini.... Pisces get shifted to Aries.


That seems a little complicated. Can you simply pull a random message for every day for every zodiac sign?

No...This:

For example: Virgo goes to Aries, Aries in Virgo, Leo goes to Taurus, Taurus to Leo, Cancer to Gemini, Gemini to Cancer. Pisces in Libra, Libra in Pisces, Aquarius in Scorpio, Scorpio in Aquarius, Capricorn in Sagittarius, Capricorn Sagittarius.

 

[MajP]

For example: Virgo goes to Aries, Aries in Virgo, Leo goes to Taurus, Taurus to Leo, Cancer to Gemini, Gemini to Cancer. Pisces in Libra, Libra in Pisces, Aquarius in Scorpio, Scorpio in Aquarius, Capricorn in Sagittarius, Capricorn Sagittarius.

Even more complicated. I do not get the point of swapping message sets. What is the purpose.
1. So you are saying to create 9 days of random sets of messages
2. After nine days you swap messages between two pairs

What happens after day 18? Do you start over? Swap with a different pair?

 

[KrIs86BsBG]

Even more complicated. I do not get the point of swapping message sets. What is the purpose.
1. So you are saying to create 9 days of random sets of messages
2. After nine days you swap messages between two pairs

What happens after day 18? Do you start over? Swap with a different pair?

I have many texts for each of the categories and I can put them. For example, 100 text for each category individually. Every 9 days, the lyrics are rotated in the sequence I wrote about the signs and so infinitely ... Can a form be made from which to choose a drop down menu: Year, Month, Date and Sign. A certain date of the month and year is selected with a certain sign and subtracts for all 12 zodiacs the text in a WORD document so that it can be copied? It's important to do to make it easier. Just like the shape itself is not locked to make changes to the lyrics?

 

[MajP]

I have many texts for each of the categories and I can put them. For example, 100 text for each category individually.

1. I understand that to mean that 30 messages is only an example and it can grow larger. So the code needs to check for the amount on messages and not be set to 30.

Year, Month, Date and Sign. A certain date of the month and year is selected with a certain sign and subtracts for all 12 zodiacs the text in a WORD document so that it can be copied?

2. Yes, but I would use a calendar control which is easier than coding or using separate drop downs for year, month, day. I do not understand once you select a date and sign what you mean by subtracting all 12 zodiacs and copying from a Word document.
3. I am sorry, but still not sure what to do after 9 days, 18 days. it is getting lost in translation.

Would the demo work if you had an easier way to select a date, such as a calendar control?

 

[KrIs86BsBG]

1. I understand that to mean that 30 messages is only an example and it can grow larger. So the code needs to check for the amount on messages and not be set to 30.

2. Yes, but I would use a calendar control which is easier than coding or using separate drop downs for year, month, day. I do not understand once you select a date and sign what you mean by subtracting all 12 zodiacs and copying from a Word document.
3. I am sorry, but still not sure what to do after 9 days, 18 days. it is getting lost in translation.

Would the demo work if you had an easier way to select a date, such as a calendar control?

1. Yes for more than 30 for each category.
2. Well let's have a calendar. After selecting the specified date, month and year, press a button to display the information for all 12 signs arranged one next to the other:

Aries
Description_Love
Description_Working
Description_Finance

Taurus
Description_Love
Description_Working
Description_Finance

and so for each of the signs. And all this information is displayed in a document that can be opened and kept in Word.
3. Example: We have 100 text for each category = 300. Every 9 days, all texts pass from one sign to another in random order: Zodiacs are 12 in number and we have the following random combinations:

6-1, 1-6; 5-2, 2-5; 4-3, 3-4; 12-7, 7-12; 11-8, 8-11; 10-9, 9-10.
12-1, 1-12; 11-2, 2-11; 10-3, 3-10; 9-4, 4-9; 8-5, 5-8; 7-6, 6-7.
3-1, 1-3; 4-2, 2-4; 6-5, 5-6; 8-7, 7-8; 10-9, 9-10; 12-11, 11-12.

and many more similar combinations. Similar to texts in each category from 1 to 100 and 100 to 1 are countless combinations. It is important for 30 days (1 month) that there is no repetition of texts for each sign.

I submit sample images of how the database and the extracted information in Word document appear after the data is filled in:

*I apologize for the language of the pictures because I am a Bulgarian and the language of Access is in Bulgarian.

 

[MajP]

This seems very, very complicated, so let me see if I understand.

1. For each 12 zodiac signs generate 9 days of random non repeating sets of messages.
2. After every nine days swap the sets of messages between pairs of zodiacs. The pairs are picked randomly.
3. Since there are 11 possible swaps do step 2 eleven times and ensure the swaps are not repeated.. (This would create 99 days of horoscopes. 12 groups of 9 message sets swapped 11 times)
4. After 99 days generate 9 new days of random non repeating sets of messages and repeat the entire process.

I am not saying that is not possible, but pretty complicated and confusing. Can you explain the reason for swapping messages, instead of just generating random messages. That would be far, far easier. Hopefully someone can interpret this better. I can code the above as described, but since it is so confusing I may be the only person who will be able to understand the final code.

Saving the results to Word or PDF should be simple if you make a report. Did you look at the demo?

 

[isladogs]

Kris
Pardon me for intervening but it seems you are expecting someone to write a fairly complex database for you for free. As it is likely this has a commercial purpose that seems somewhat inappropriate.
Everyone here is a volunteer. Whilst someone may be willing to do this for you, that isn't the primary purpose of a forum. Usually we provide guidance to help you complete a task and in doing so, help you to become more proficient in Access.

 

[KrIs86BsBG]

This seems very, very complicated, so let me see if I understand.

1. For each 12 zodiac signs generate 9 days of random non repeating sets of messages.
2. After every nine days swap the sets of messages between pairs of zodiacs. The pairs are picked randomly.
3. Since there are 11 possible swaps do step 2 eleven times and ensure the swaps are not repeated.. (This would create 99 days of horoscopes. 12 groups of 9 message sets swapped 11 times)
4. After 99 days generate 9 new days of random non repeating sets of messages and repeat the entire process.

I am not saying that is not possible, but pretty complicated and confusing. Can you explain the reason for swapping messages, instead of just generating random messages. That would be far, far easier. Hopefully someone can interpret this better. I can code the above as described, but since it is so confusing I may be the only person who will be able to understand the final code.

Saving the results to Word or PDF should be simple if you make a report. Did you look at the demo?

1. Yes
2. Yes.
3. Yes.
4. Yes, but in the execution of the form every day to exchange these 100 texts in each category and for at least 30 days there is no repeatability of texts for the different zodiac signs. The reason for the exchange is that the composition of the horoscope takes the order of the 9 planets and for every day, and so the different horoscopes are obtained. To see what you've done super is mixed up, the question is if you can and how to get the information from the form in a WORD document, as I showed in the picture?

 

[KrIs86BsBG]

Kris
Pardon me for intervening but it seems you are expecting someone to write a fairly complex database for you for free. As it is likely this has a commercial purpose that seems somewhat inappropriate.
Everyone here is a volunteer. Whilst someone may be willing to do this for you, that isn't the primary purpose of a forum. Usually we provide guidance to help you complete a task and in doing so, help you to become more proficient in Access.

I do not have such commercial intentions, this is my assignment, which I can not handle myself, so I ask here.

 

[isladogs]

I still think you are expecting someone to do all the work for you. That way you will learn very little.

 

[MajP]

To see what you've done super is mixed up, the question is if you can and how to get the information from the form in a WORD document, as I showed in the picture

You can export a report into RTF format which is readable in Word. If the formatting is not too complex it should export well. If the report is complex the export to RTF may not be perfect. If you export to PDF it will export exactly. So build a report based on qryHoroscope. You can use Sorting and Grouping to get it in the correct format

Date
  Zodiac Sign
    Love Message
    Finance Message
    Work Message

 

[MajP]

Please read.
https://docs.microsoft.com/en-us/office/vba/api/access.docmd.outputto
https://docs.microsoft.com/en-us/previous-versions/office/developer/office-2003/aa220433(v=office.11)

 

[MajP]

Something like this

 

[KrIs86BsBG]

You can export a report into RTF format which is readable in Word. If the formatting is not too complex it should export well. If the report is complex the export to RTF may not be perfect. If you export to PDF it will export exactly. So build a report based on qryHoroscope. You can use Sorting and Grouping to get it in the correct format

Date
  Zodiac Sign
    Love Message
    Finance Message
    Work Message

I will try well. And how do I do with the template, as I showed it in the picture?

 

[MajP]

And how do I do with the template, as I showed it in the picture?

If all the data comes from the database then just make a report and format it to look like the template. Export the report to RTF or PDF. See if the formatting is maintained.

If it has to be in Word with very specific formatting you can also try using mail merge
https://support.office.com/en-us/ar...-to-word-053cc639-fe30-4d3a-943d-0bee0892f16a

 

[MajP]

This version demonstrates exporting to Word (RTF) and a PDF.
You can select any date simply by clicking in the field and using the datepicker. Or you can type in a date. It builds as many horoscopes (random non repeating) as there are messages.

It does not do the 9 messages, random swaps, which as I Said would be very complicated and not very random.

 

[KrIs86BsBG]

This version demonstrates exporting to Word (RTF) and a PDF.
You can select any date simply by clicking in the field and using the datepicker. Or you can type in a date. It builds as many horoscopes (random non repeating) as there are messages.

It does not do the 9 messages, random swaps, which as I Said would be very complicated and not very random.

The buttons on the form do not work after pressing ...

After pressing, he writes an error: Run-time error "3075"

Syntax error in date in query expression "(HoroscopeDate = #04.25.2019#)"